Multiple Use of a foreign key in a table

[CosmicKid]

Hi all,

I have a form for entry of telecom circuit info. The bulk of the info is stored in table "Circuits_Table". The table needs to access another table twice to display contact info for contacts on both the A and Z end of the line. This info resides in a table "Contacts_Table", pk is "ContactID" field.
I would like the form to autofill with contact info when the user selects a contact name for each end. To this end, I created to fields in the "Circuits_Table" - "LocAContact" and "LocZContact", but I need to relate them both back to the same "Contacts_Table" ... can this be done? or more importantly should it?

I know I can do this with a single instance of contact info by basing the form on a query that pulls the necessary info, but...I need it to pull the data twice. Can't seem to make that work.

Any advice, insight, workarounds, general musings, redirects would be mighty appreciated.

Additional info can be supplied if needed.


-CK

 

[wazz]

edit: welcome to AWF.
and...looking again, i'm afraid you might have to enter the same Contact twice for a Circuit. is that so? or is it always two different Contacts per Circuit?

'''''''''''''''''''''''''

this might require a redesign of your tables, actually, just adding another table.

Circuits can have many Contacts, so it's a 1:N (one-to-many) relationship.
but, if Contacts can be applied to many Circuits then it's 1:N in the other direction, too.
so, a third table - a junction table - would be required in this case:

Circuits_Table --> CircuitsContacts_Table <-- Contacts_Table

CircuitsContacts_Table
CircuitsID (PK)
ContactID (PK)

make both fields the primary key and you will not be able to enter a duplicate "pairing", but you will be able to add any number of Contacts to a Circuit:

CircuitsID   ContactID
1              1
1              2
2              11
2              32
3              1
3              1  <-- automatic error for duplication of the PK.
4              32
4              15

your form can be based on Circuits (one per record) and a subform can show Contacts (many per Circuit)

hope that works.

 

[CosmicKid]

great info - thanks. The subform idea was what I was missing. Works as planned now.

CK