How set up data base....

[sjl]

I am just beginning to design a small data base, where end users will mainly use an Access Form to search and view data.

The (lab) data will be arriving to our group occasionally over the next 2 years, and will be put into an Excel file (formatted the same, each time). The new data could be appended on the end of a master file...so I could link just one file to a table in Access. I need to build a Form, based on this data.

But, I am not sure if I should link the file from the beginning (since I've read that I cannot change data types or field sizes, if I link)....

Maybe I should import one existing file, manipulate the table parameters as needed, then design my Form....then link later??????

what would you do in this situation?

thanks!

 

[Rabbie]

Will the data in the Excel file be normalised. If not, you will need to split the data into different tables. Remember that Access is a relational database system and Excel is a spreadsheet program. If you don't understand Normalization then read about it in Access help and do a google search. There are also many links about it on this forum.

 

[sjl]

Rabbie,
Thanks.

The table will have edit checks done on it (in SAS) to catch duplicates and other anomalies. A cell's value may be corrected (rarely) once it is in the database.

The file will have only these variables:

Misc ID (Primary)
Subject ID (also unique, could be primary but will just make one primary)

Visit number
Date Drawn
Material Type

Comments

A Misc ID (or Subject ID) will have up to nine visits and dates when samples are drawn. Each will have up to 4 material types.

 

[Rabbie]

Rabbie,
Thanks.

The table will have edit checks done on it (in SAS) to catch duplicates and other anomalies. A cell's value may be corrected (rarely) once it is in the database.

The file will have only these variables:

Misc ID (Primary)
Subject ID (also unique, could be primary but will just make one primary)

Visit number
Date Drawn
Material Type

Comments

A Misc ID (or Subject ID) will have up to nine visits and dates when samples are drawn. Each will have up to 4 material types.

Your data is clearly not normalised.(Repeating Visit data and repeating Material types). This WILL cause you problems as you progress. What will happen if someone decides you need to have 10 visits or 6 material types.

You need to have at least 3 tables.

One for Misc info

one for Visit info holding a Foreign Key for the correct record in the Misc table

One for the Material types also linking back to the Misc table.

You will find it well worth while to read up on Normalization for database tables.

 

[twoplustwo]

http://support.microsoft.com/kb/283878

http://www.access-programmers.co.uk/forums/showthread.php?t=137668

http://www.access-programmers.co.uk/forums/showthread.php?t=121666

http://209.197.234.36/db/simple.html#label4

 

[twoplustwo]

http://datamodel.org/NormalizationRules.html#one

 

[twoplustwo]

That last link is probably the best, along with the microsoft one.