Secondary field lookup

[Chris54]

I have a table called Job Codes/Job Titles. I listed the job codes (numbers) in the primary field and then the job titles in the secondary field. In another table I added the Job Codes/Job Titles as a linked field. I want the ability to look up a Job Code (in this linked field) by its Job Title in case I can't remember the Job Code.  Right now I have to start typing in the Job Code number (but we have hundreds of Job Codes). Is there a way to do this other than making the Job Title the primary field?

For instance, I may have a job code 0004 with a job title Academic Intern. In the linked field Job Codes/Job Titles I want to be able to start typing in Academic Intern and have it bring up both the job code (primary) and title (secondary).

Thanks for your thoughts.

[BillH · Accepted solution]

If you are at the beginning of setting this up you can duplicate the Job Code field, then change the original to a formula combining Job Code and Job Title.  You will probably want to modify the names of the fields and clean up the old link field in the second table.  I've done this with a similar situation and it worked fine, but I wouldn't jump into it if I had a large amount of data already or additional dependencies on the key field.

Your lookup should be something like this

[Chris54]

Thank you BillH - I'll give it a try!