Very Large Access Tables 2

[johncefas]

Hi all

Im new to this and would not descibe my self as a access buff. So i am having a problem in how to design a database.

the issue is that i am a scientist and i want to create a database of specimens (now each secimen can be put into several catagoies ie fish, crab etc. But the specimens are identified by a internal code consisting of an accession number (may refer to more than one specimen)the specimen number within that accession and the group in to which it fall within that accession.

Because of this i want to assign a single ID which individually identifies that specimen in a single field unlike the present above situation.

The next problem is that I can create this easilly but I have to collect alot of data on each specimen, but not all data is pertinant to all species of animal. So for this I have created a table for each disicnt survey group (ie fish, crabs, rabbits etc) each of these has about 250 data fields.

I have created a table that creates the ID and links it to the other information (Ie the accession number, the specimen number and the group number)

When i enter data into the database i want to be able to make sure that the ID is present on all tables so that I can input information into differnet tables confident that the ID will be the same therefore linked every time i add a new entry to the database.

Also is this a feasible project in access if I waant to hold about 40,000 specimens?

Sorry for being so long winded but if anyone could help i would be most greatful,

Thanks

John (V.confused)

 

[lespaul]

I would first recommend that you read 'The Fundamentals of Database Design'. This can be found at TT JeremyNYC's website:

http://www.alphabetcitydataworks.com

Once you have read the fundamentals, review what you've got, see if you need any modifications to keep to third normal form (3NF) and then post your tables' structure for our review.

HTH

leslie

 

[BNPMike]

It is perfectly feasible. The good news is Access has many features to make this work for you.

The bad news is Access has many features to make this work for you. It's a lot of learning. My brand spanking new Access 2003 Inside Out manual is 1294 pages long.

It may be worth getting someone to do this for you. It's not a big job once you know Access.

 

[johncefas]

thnaks guys will read on, thats the trouble with scientist want the big picture before workingon the fundamentals!

john