Access Database - LookUp a field value

  Gary Wood 15:24 01 Jun 06
Locked

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?

Thanks,

Gary

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…