Best DB practice to use when main table has two fields that relate to information in separate table

[Lakeside]

Limited knowledge of Access. In my main table, I have two fields, one to indicate primaryorigin and the other a secondaryorigin. A contactID number is input for each field for records that matches to a primary key = contactID from a ContactList table (name, address etc.). Example, for record #1, the primary origin might be 3 (George from USA) and the secondary origin might be 50 (Mr. Fields from Canada) and so forth for the rest of the records in the main table. How does one set up a relationship/connection when two fields in one table point to the same ID table (list of contact information)? Possible or better solution? Thanks!

[June7]

Include the lookup table twice. Relationship and query builder will assign alias name to the second instance like ContactList_1.

The alternative is two records in a related dependent table for each record of 'master' table.

The latter is most normalized structure and would be preferred if you want to allow any number of dependent records.

It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." Your 2 fields can work. A challenge with this structure would be searching for a contactID that could be in either field but with only 2 fields is not so difficult.

[Lakeside]

Thank you for the information/reply. I will keep the quote in mind for future Access endeavors .