Table with composite key

[Guest]

Hi

I have a table , called Table1 with following fields :
A Partial Primary Key
B Partial Primary Key
C
D

Also my Table2 holds these fields :

V
G

I must add to Table2, fields A and B as foriegn keys to Table1, In a way
that they show me the same values I have in each row (A,B) , I get them in my
Table 2 also the same combination of (A,B).

I tried using a lookup table , it shows two fields, but it only stores one.

Could you please tell me what is the best solution ?

Thanks
Daneil

 

[Tim Ferguson]

I must add to Table2, fields A and B as foriegn keys to Table1, In a
way that they show me the same values I have in each row (A,B) , I get
them in my Table 2 also the same combination of (A,B).

I tried using a lookup table , it shows two fields, but it only stores
one.

I am not quite sure what you mean here by a look up table, but the solution
is exactly as you have put in the first half.

- Add the fields A and B to the second table, making sure they match the
types and sizes exactly of the Table1.A and Table1.B;

- In the relationships window, open both Table1 and Table2. Ctrl-click
fields A and B in Table2 and drag them both to Table1. If everything works,
the dialog will open with the correct fields already selected. Make sure
that RI is selected and OK to save. There will be a paired line on the
relationships window to indicate a two-field relationship.

Hope that helps


Tim F

 

[Jeff Boyce]

Daniel

Tim's solution offers a way to "migrate" the multiple key fields to the
"child" table.

If I understand your attempt to use a lookup field type in your Table1,
don't! You've discovered one reason not to (it doesn't handle multi-field
keys)... another is that it shows one value but stores another ... and one
more reason not to - tables are great for storing data, but don't use them
for display (that's what forms are for).

Another approach (not better/worse) would be to use a single field primary
key (an autonumber if you don't have a strong natural key to use). This
lets you use a single field foreign key in Table2.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/