Combo Box Lookup Fails

[xweyer]

I have a database with three tables Dept, Staff & Request with the following relevant fields...

Department Staff Request
Dept ID SaffID Staff
Dept Name SLName
SFName
Dept

The field Dept in the staff table is a query based lookup field back to the Department Table. Data is entered into the Dept field in Staff via a combo box which displays Dept Name and enters the corresponding Dept ID.

The problem arises with the third table. I want to use a combo box to display text in the form of "John Smith Accounting" in order to enter the corresponding Saff ID number and have created a query on Staff that produces that output correctly. However when I try to setup the Staff field in Request as a lookup based on this it the combo box displays "John Smith 12" instead.

Is there a way to get this to work.
[sig][/sig]

 

[Guest_imported]

Try this type of sql.
Table1 is where the DeptID and Dept Name are.
Table2 is the table with the name info.
Just replace the table names and field name to match your tables and you get a field called Title, this works!!

SELECT Table1.DeptID, Table2.FirstName & " " & Table2.LastName & " " & Table1.Department AS Title
FROM Table1 INNER JOIN Table2 ON Table1.KeyID = Table2.DeptID;

John

[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[xweyer]

Yes it did. Thanks. [sig][/sig]