Relating 2 Tables - Relational Problem

[jeng]

Hi,

Im having a problem with designing a database realationship between 2 tables.

I have 2 tables; 'Composition' and 'Tx Test results'.

'Tx Test results' has "Transformer ID" as a primary key, and 'Composition' has a composite key of "Transformer ID" and "Date Tested".



In Access, how do i relate the composite key (I.e. the combination of Both Transformer ID and Date Tested) to the Transformer ID in the 'Tx Test Results table'

Thanks.

[RuralGuy]

Why are you using a composit key for the Composition table? Are there many entries in that table with the TransformerID in a field?

[jeng]

Yeh thats right RuralGuy,


It contains entries of the same transformers in there, tested on different dates.

So, Transformer 1 can be tested on 4 different days in the space of a year. While Transformer 2 may have only been tested once.

Therefore, it is the combination of Transformer ID AND Date which uniquley identifies a record.

....Any ideas on how to relate the two tables?

Thanks very much!

[RuralGuy]

The two tables sound like they have a 1:M relationship with each other and you would use the TransformerID field to define the relationship. You would display this relationship with a MainForm/SubForm arrangement with the MANY side on the SubForm in Continuous Form view. No date involved in defining the relationship.

[jeng]

Thanks for the reply RuralGuy,

Are you saying, in order for me to establish the relationship between the two tables, that i would have to:

Click on the relationships menu from the toolbar in Access > Drag the PK (Transformers) from the Composition table, to the PK (Transformers) in the 'Transformer Test Results' table?

When i do this i am given an error that goes along the lines of "You have duplicates in the PK field"

This is because the Composite Key is comprised of Transfomer AND Date.

How do i relate the composite key of Transformer AND Date (in the compositions table), to Transformer in the (Transformer ID Table)?

Cheers.

[RuralGuy]

I would *not* use a composit key. I would let each table have an AutoNumber as a PrimaryKey and the ManySide table would have a ForeignKey field named TransformerID with the appropriate LongInteger value from the other table in it. You do not need nor should you use a date to relate these two tables.