Lookup field needs to convert numbers and show text

[Jeff H]

Still in the early stages of converting my Excel database to Access (Office 365).

I imported the Activity table from Excel. It records each volunteer/patient contact. The type of service provided is designated by a number in the imported table. I want to make that a lookup field in the Access Activity table, using the numbers but displaying text.

I created a table called Services with two fields: Code and Service. The number of the service is in Code and the description is in Service.

The Service field of the imported data contains the service by number, so of course that value has to be maintained for the conversion. But I want the Access field to be a combobox that shows both Code and Service, then displays the text from the Service field of the Services table.

I’ve tried several tests using the lookup wizard and manually setting the lookup in edit table view, but I can’t get it to work.

[HansV]

Open the Activity table in design view.
Select the Service field.
Activate the Lookup tab in the lower part of the table design window.
Select 'Combo Box' in the 'Display Control' dropdown.
Select Services in the 'Row Source' dropdown.
Set the 'Column Count' property to 2.
Set the 'Column Widths' property to 0.
Save the table design.
The field will display the description, but store the code.

[Jeff H]

How should I link the Activity and Services tables?

I tried first without setting up a relationship, then 1 to Many in each direction and in each case the field and dropdown were blank. I assume that's because of the 0 Column Width, but when I give it an 1" it displays the numbers. The Bound Column is 1 by default but I set it to 2 and it didn't help.

Incidentally, each service can appear in many activities, and each activity can have more than one service. I've been trying to just set up a basic lookup first, but that many to many relationship is an issue I'll need to deal with.

[HansV]

Did you set the Column Count to 2?

[Jeff H]

Yes, Bound Column 1, Column Count 2, Column Width 0.

The services table actually has 5 fields. In the Lookup Wizard I was able to specify the ones I wanted (Code and Service, though I see that the wizard automatically includes ServiceID with a 0 width). So I'm curious in manual setup how the columns are identified. The fields are:

ServiceID
ActivityID_FK (foreign key)
Code (numeral)
Service (text description)
Qualifier (yes/no, indicating whether a service requires certain qualifications)

[HansV]

For a manual setup, you have two options:

1) Set the Row Source to a SQL string:

SELECT Code, Service FROM Services ORDER BY Service

- or -

2) Create a query that selects Code and Service from the Services table, save this query and set the Row Source of the Service field in the Lookup tab to the name of that query.

[Jeff H]

Excellent. I took the query option and it worked. Thanks!