I'm creating a database which records details of many teams of people. Each team has one record, one of the fields within which is a lookup field. The Lookup allows the selection of the team's supervisor from a "Supervisors" table.
The supervisors table contains, amongst other things, the following fields: ID, Title, First Name, Surname.
When creating the lookup field, I chose to hide the key column, because it contains a random autonumber which is meaningless to the user. So, when the lookup is now used, the First Name value is displayed in the Teams table (though it must actually be storing the key value somewhere, because supervisors with the same First Name are not causing problems).
The problem with this is that the First Name alone is not enough to identify the supervisor. I need the Teams table to show the Supervisor's First Name and Last Name (preferably in one column and separated by a space).
I've tried to do this by creating a Query of the supervisors table with a field that strung together the first name and last name. However, when I set the lookup to find values from the query, it doesn't give me the option of hiding the key column, so that only value I can return to the Teams table is the key value, or the name (but then I can't have multiple people with the same name).
Can anyone help me achieve what I'm trying to do?
This thread is now locked and can not be replied to.