I have 2 tables :
SSN_NAMES Table (names with social security number)
TRANSACTIONS Table (where tranactions for customers are stored.
These table are joined by SERIAL fields
The transactions table has 6 name fields in it - name_1 thru Name_6. The transactions table also has 6 soical security number fields - SNN_1 thru SNN_6.
What I would like the user to be able to do is when they are in the TRANSACTIONS table, and they enter a social security number in SNN_1, pull over that person's name into thre NAME_1 field. When the user goes to the SNN_2 field and enters a valid sociel security number, pull over the person's name ( associated with this social security number) for NAME_2, and so on.
I have tried to do this with a value list in the names field and also tried with a lookup function for these fields, but when I use a look up function in the name fields, and I type a social, it puts the same name in all name fields.
How do I get it to pull the matching name for each social entered:
in TRANSACTION Table - ssn field, type 123456789 and in the TRANSACTION Table - name field, autofill with name os person matching the entered social.