I want to create a Query based on people's names.
In one multiple record table (Table 2), I want to select people's names from a single record table (table 1), and pull associated data from table 1 into the records in table 2.
One way I tried this was to create a name query for Table 1 and attach it as a Lookup field in Table 2. I have since seen that Other Table/Query Lookups are ill-advised. Also, I can't create a Criteria Query for the name in Table 2 (probably because it's based on a Lookup).
Currently, I am using an internal "I type the values" lookup in each table and using combo boxes in the associated Forms. That works, but will be labor intensive going forward as I'll have to manually update the fields in both tables for each new entry in Table 1.
Is there a cleaner way to do this?