Help with a Query

Please could someone help with the following problem...

Here are the tables relevent to my question:

tblProjects
*ProjectID
ProjectTitle
StartDate
EndDate

tblEmployees
*EmployeeID
LastName
FirstName
Etc...

tblAssignation
*AssignationID
ProjectID
EmployeeID
AssignedHours

When the user is entering data into the assignation table, i want them to be able to use a combo box to view the EmployeeID field, only i don't want them to see the EmployeeID, i want them to see the corresponding LastName. And in reverse, when they select the LastName i want the corresponding EmployeeID to be entered into the record.

Any ideas would be much appreciated.

Thanks in anticipation.

What type of form is this? HTML, MS Access, Visual Basic, .Net? If it is HTML you can set the value equal to the employeeID. If it is one of the others you will need to perform a query to resolve the name to the id. You will need to handle duplicate names though.

In MS Access format.

I want to create the query in the lookup tab of the table's design view.

How will you handle the possibilty of two Joe Users?
When a user highlights a name and submits the form you could:
Run a query to resolve the employeeID
select employeeID from employees where name = [passed_value]
Then create your insert statement
Insert into assignment values(employeeID, assignment)

Victor,

Thanks very much for taking the time to think about my question and post you replies.

I've managed to solve the problem, taking on board your suggestions for multiple entries of the same name.

Thanks again.