+3  A: 

If you are entering the data via a form, then you create a drop down list that uses two columns for it's value list (CredTypeID and CredType) and then set the width of the first column to zero. Hey presto, a field that access treats as having a value of CredTypeID, but displays with CredType.

I don't think you can use this trick directly in the query results themselves, though.

Actually you can use the same trick directly in the query results if you set the columns up in the table design. Another benefit of doing this is that any new forms the field is added to will automatically be created with the drop-down.
+1 as it is correct, but the accepted answer had more detailed instructions. Thanks for your help
+4  A: 

This should do the trick, will work in datasheet view and auto-set up the field as the type of dropdown you want if you add the field to any new forms.

  1. Open the Lawson_Employees table in design view.
  2. Click on the CredType field and at the bottom of the screen switch to the "lookup" tab
  3. Change DisplayControl to "Combobox
  4. Change the Rowsource to be the following query:


  5. Set columncount=2

  6. Set Columnwidths to "0;"
  7. Set LimitToList = Yes
  8. Make sure BoundColumn is set to 1

If you have already added the Lawson_Employees.CredTypeID field to a form, delete it and then re-add it to get it to automatically set it up so you can select by the friendly label instead of the id.

Very nice. For those of us still using Access 2003, a rigth click on the column name gives the "Look up column..." option.
I didn't know that was possible in a linked table, but that's mostly because nobody in his right mind would define a lookup in a table. Lookups are USER INTERFACE objects. Tables are not UI objects. Create a query or a form and put the dropdown there. It also causes bad problems because the UI doesn't align with the actual stored data. On the evils of lookup fields:
@David good info, and this is a short term solution. We started with Numerous data sources. Note I didn't say databases because some people were/are using spreadsheets to keep up with the information. I don't have a complete UI yet and won't until we have migrated completely to sql server and will have a completely web interface then. In the short term to keep things working and keep up with the data I am using access as a front end for the tables that are now on sql. Looking back we really should have said to hell with the legacy data and started from zero.
Even if we started from zero we would still have to interface with our HR system which is a poorly designed system to say the least.
Accepted answer, this did work and had very detailed instructions.
That arguments in that article about the evils of lookup fields are dubious at best. There is nothing inherently wrong with lookup fields.
In fact, the advice in that article is so bad, I just wrote up a blog-response refuting every single point in it. If you are interested:
@JohnFx: Nice article.