I am wanting to develop and create a program/system at work using vb and access to db to store lots of information and be linked to scanned files, or store them as links

My database needs lots of tables, many will have already pre-determined txt and so selectable, Using a form, these shall be selected, other fields entered, then posted into one database record

I need to get my database tables organised a bit more, below is my first attempt and know its wrong ,can someone give me some pointers where ive noted am not sure of



If in my database tables that will be used to create drop down lists
I have a list of 1-13 titles, each one has many subsections
e.g
1. DCC (main sections
1/1 gen
1/2 miners
1/3 questionnaires

should these then be in separate tables, how best is it to display them as for the first table im wrong.

Filing Room indextbl
file id(primary key)
filing room subject

and not sure for the second table to cope with the subsections

file id
filing room sub sections??


I will have 4 table stored, all are road types
District
ARoads
BRoads
CRoads
they all have different lists of roads per category.

e.g Districts - Ch, Der, Du, Dh
A - a1, a177, a181
B - b1287, b 1289
C - c10 , c15, c184 etc

Can I do

District tbl - distrct
A road Tbl - road number
B road Tbl - road number
C road Tbl - road number

then Road tbl
Disrict
A Road
B Road
C Road
(linked together to the toher original tables, lookup)

In 3 tables, i want these to have a road numbered stored in each record, chosen via a form with drop down lists.
e.g
RC tbl
road
rc number
date

PLItbl
road
claimant
accident date

how can i design it better as i know its wrong

On my program i will have district, aroads,broads,croad buttons
When selected a forum will load and will have two drop down lists already from stored tables, so e.g when B roads is selected, its drop down list will hold all of the b road numbers stored and so want that road number selection and other fielsds posted into another database table
e.g
rctbl
road
rc number
date

Can anyone give me some pointers on how to tidy up what ive planned, I havent set up any database or tables as yet, but know what i want my program to do, in relation to what information will be submitted, where it will be stored, its just organising these tables with stored already determined data

Ive just been looking at the northwind database, I see how all the tables have relationships, but i cant really see how i can create enough ID's that will enable me to directly link everything together, as lots of the tables will generally hold data lists that will never be changed, and wll just need to be selected, rather than having them manually typed in every time in each record
hope u can help

thanks

Lee

Lee

Just a few pointers - from reading your specification i gather that you are looking to design your database whereby new entries are generated from standard information.

Firstly I would design the tables for the information that doesn't change eg:

tblroads should have the following: PrimaryfieldNo,District,RdLetter,Number. The relationship on this table would be one to many from the table that contains the tblclaimant which would also have a primaryfieldno.

The tblclaimant as I understand you is a changing feast, and therefore this would be the table that has a many to one relationship and the primary key in this table would be linked to the primaryfieldno of tblroads.

Overall it is very worthwhile writing your table design manually and then seeing where the relationships are, it doesn't matter how many tables you have, in fact the more the better as it makes design changes easier. I would recommend getting the Wrox Expert one-to-one Access Application Development by Helen Feddema as this gives some great examples of table design.

Hope this helps

taraj

i have made some changes to my database tables, Is this better?

Heres my tables and some of the fields inside


Filing Room input.tbl (recordnumber, title, start date, end date, from, to, no of pages)

filing room index.tbl (file ID, title)

RC.tbl (recordnumber,Road, rc numb, accident date, from)

PLI.tbl (recordnumber,Road, Claimant, Accident date, from)

Correspondance misc.tbl(recordnumber, road, file ref, file subject)

57list.tbl (file ref, file subject)

Road.tbl(road ID, Road)


The information inside Filing Room index.tbl, 57list.tbl & road.tbl will all be preset stored tables, where these will be used on the main tables to lookup and make a selection (drop down lists on forms eventually)

The recordnumber field in Filing room input.tbl, rc.tbl, pli.tbl and correspondance misc.tbl is so that each record entered into these tables can have a unique record number to identify them. Using the autonumber function on all three, i am still going to need some way or making sure that no number is already in a different table, and so would like each of those tables mentioned to be have some way or identifying what table the number relates to, e.g rc01....., pli01...., corr01. If this is possible, or would I have to manually create the new reference on Entry?


The Road.tbl will have a long list of different combinations of lettered and numbered roads, ive added the roadid, field to makes sure that the same one isnt added into here twice when i enter the stored data to be used in the other tables.

Is this design looking better?