Table and look up Wizard

[Teri Bridges]

I am having look up issues, I have posted pics but basically I am looking up contacts from my contacts query. I have such a hard time explaining.
I want to look up Jane, I want the lookup box to show two columns Jane's name and her job title, When I click the drop down I see Jane, but only a number for the job title. If I run the query it shows the text.

If I change the look up code from Job title to company It displays Jane and the company name. I noticed a bit of difference in how the lookup wizard wrote the code but I do not know what exactly is wrong. Any help. By the way you guys have been wonderful.

Example

Contact	Job Title
Jane	7
Contact	Company
Jane	Hershey

 

[arnelgp]

you create a New Query from [active contacts Extended] Query and your "Title" table.
join them by [active contacts extended].[job title] and "title table".ID (which ever is the autonumber):

select [active contacts extended].[ID], [active contacts extended].[contact name], [theTitleTable].[job title]
from [active contacts extended] Left join [theTitleTable]
on [active contacts extended].[job title] = [theTitleTable].[ID];

see Query1 in design view.

the source of TaskT form is Query1.
view the form.

 

[Pat Hartman]

If you are using a table level lookup for job title, this is one of the reasons that is a bad idea. Remove the table level lookup. It will not break any form/report that is using a combo. Use a query with a join as arnelgp suggested to get the text value of the job title