Help with Query design in MS-Access
ms-access, sql
Solution
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.
- Open the Lawson_Employees table in design view.
- Click on the CredType field and at the bottom of the screen switch to the "lookup" tab
- Change DisplayControl to "Combobox
Change the Rowsource to be the following query:
SELECT CREDTYPEID,CREDTYPE FROM tblCredTypes ORDER BY CREDTYPE ASC
Set columncount=2
- Set Columnwidths to "0;"
- Set LimitToList = Yes
- 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.
Problem
CredTypeID is a number the CredType is the type of Credential I need the query to display the Credential in a drop down list so I can change the credential by selecting a new one. Currently I have to know the CredTypeID number to change the Credential. I just want to select it from a drop down list. Currently to change Betty Smith to an RN I have to type “3” in the CredTypeID. I just want to be able to select “RN” from a drop down list. Here is the table layout and sql view (from access) ``` SELECT Lawson_Employees.LawsonID, Lawson_Employees.LastName, Lawson_Employees.FirstName, Lawson_DeptInfo.DisplayName, Lawson_Employees.CredTypeID, tblCredTypes.CredType FROM (Lawson_Employees INNER JOIN Lawson_DeptInfo ON Lawson_Employees.AccCode = Lawson_DeptInfo.AccCode) INNER JOIN tblCredTypes ON Lawson_Employees.CredTypeID = tblCredTypes.CredTypeID; ```