Help with Query design in MS-Access

Answer 1

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.

Answer 2

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:

   SELECT CREDTYPEID,CREDTYPE FROM tblCredTypes ORDER BY CREDTYPE ASC

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.