Linking tables PLEASE HELP

E

enrico1982

I am creating a database to record tests for eletrical units. Each unit has a
unique table for the results of its test. Each table also has three common
fields which appear in each table - serial no, date and person who entered
the data.

This is where my problem is. I need these common fields to be linked so when
anyone enters a test, the serial date and person is stored in 1 master table.
I have tried creating relationships but as i have found, new records are not
created in the master when they are in the others. I have also tried
splitting the field so that the common fields only appear in the master but
then my forms wont send info to more than one table.

Any help would be great as this is vital and urgent.

[email protected]
 
B

Brian

enrico1982 said:
I am creating a database to record tests for eletrical units. Each unit has a
unique table for the results of its test. Each table also has three common
fields which appear in each table - serial no, date and person who entered
the data.

This is where my problem is. I need these common fields to be linked so when
anyone enters a test, the serial date and person is stored in 1 master table.
I have tried creating relationships but as i have found, new records are not
created in the master when they are in the others. I have also tried
splitting the field so that the common fields only appear in the master but
then my forms wont send info to more than one table.

Any help would be great as this is vital and urgent.

[email protected]
Click to expand...

This sounds like a terrible design. If you could post more info about your
testing process, I'm sure someone could help you improve it dramtically.
 
E

enrico1982

Maybe but thats the process so i need some coherant way of databasing the
entrys.
 
J

John Vinson

I am creating a database to record tests for eletrical units. Each unit has a
unique table for the results of its test. Each table also has three common
fields which appear in each table - serial no, date and person who entered
the data.

This is where my problem is. I need these common fields to be linked so when
anyone enters a test, the serial date and person is stored in 1 master table.
I have tried creating relationships but as i have found, new records are not
created in the master when they are in the others. I have also tried
splitting the field so that the common fields only appear in the master but
then my forms wont send info to more than one table.
Click to expand...

THat's not how relationships work. A relationship between tables
RESTRICTS what you're allowed to enter - the "child" table cannot
contain records which do not have a match in the "parent" - but a
relationship will not (and should not) automagically create a record
in any other table. Also, it appears that you are storing this
information redundantly. If you store the data in the unit table, and
the same data in the master table, you are completely unprotected from
someone editing the data in one of the tables; you now have two
records with different data, and no easy way to determine which is
correct.

It also sounds as if you have a series of identical tables, one for
each "unit". This is called "storing data in tablenames" and is *not*
a good idea!

Might it be possible to have just one large table, with a Unit field?
You could have Queries to display the data for any chosen unit.

If you're determined to use this design, then I'd suggest using a Form
based on the Master table, with a Subform based on the Unit tables.
You can use

SerialNo;TestDate;PersonID

as the Master/Child Link Field property, and use VBA code to change
the Recordsource of the subform to the desired unit table. But I'd
really suggest stepping back and looking at the normalization
structure of your tables instead!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
B

Brian

enrico1982 said:
Maybe but thats the process so i need some coherant way of databasing the
entrys.
Click to expand...

You haven't explained how the process works. Creating a number of identical
tables is not a coherent way of "databasing" anything, but no-one here can
help you with a better design if you don't explain the process.
 
Top