Two ID fields with cross-populating Look-Up

[jebowers]

So, the Person table has 2 IDs (neither is the primary key of the table, like SSN and HRID). What the customer wants is to show both IDs in the Orders table.

I'm having trouble getting the table to populate the second ID field based on what is selected from the drop-down in the first ID field.

Person table (ID, FirstName, LastName, SSN, HRID)

Orders table (ID, PersonSSN, PersonHRID, etc.)

If I populate the Look-up field of PersonSSN as 'Select SSN, HRID from Person' (use column 1 as value),
how do I get the HRID field to populate when someone selects a SSN value from the Person drop-down?

And the follow-up, can I get the same thing to happen in reverse (i.e. populating the PersonSSN value when the customer selects an HRID from the PersonHRID drop-down)?

I'm hoping someone understands what I just wrote, even if you can't help me.

Thanks

[ssanfu]

- Are you using lookup fields in your tables?

- Why are you storing the SSn & HRID twice?

- What does the SSN & HRID have to do with orders?

- Are you using forms to enter data or entering data directly into the table?

[jebowers]

Are you using lookup fields in your tables? Yes

- Why are you storing the SSn & HRID twice? I'm not, the Person table is the authority for the SSN and HRID. The Orders table has a reference (foreign key, so to speak) to the matching ID field from the look-up query in the Orders table designer.

- What does the SSN & HRID have to do with orders? An order is issused by a Person, and the client wants to see both IDs in the table (view is not sufficient).

- Are you using forms to enter data or entering data directly into the table? The users are accessing the table directly and using the down-down to choose a person's ID.