(Programmers usually don’t use Microsoft Access in multi user apps, but it can be handy if you quickly need to setup some single user personal administration database.)
A thing that I find very cumbersome in Access, is the use of surrogate keys (e.g. autonumbering IDs instead of business keys like “name”), because autonumbering keys are not very informative. This blog post tells you how you can show informative field while at the same time keep using your existing surrogate keys.
Tested with Microsoft Access 2007.
Example of the problem
Here an example of the problem: you have a STUDENT table and a COLLEGE table, where a student has only one college, and a college can have multiple students. (A one-to-many relation.)
Now when you want to select/assign a college to a student, you don’t want to select it using NON-informative college_id’s like this:
instead you want select college_id’s using informative college names like :
And after after selecting the college_id, you want the informative college fields to stay visible in the list, like this:
while at the same time having a transparant relationship using the numbered surrogate id’s as foreign keys.
Composed of two subproblems
The problem consists of two sub problems:
- selecting non-informative foreign key id’s transparently using informative fields.
- showing (single or multiple) informative fields in the list instead of a non-informative foreign id key
Solution using Lookup Wizard
The easiest way to accomplish this is to use the “Lookup Wizard”. This wizard creates a (new) relationship between the 2 tables, so you have to make sure that a relation doesn’t exist yet.
In this case, college_id will be the foreign key and thus the lookup column.
There are two ways to access the Lookup Wizard:
- via the “Lookup Column” button. This creates a new column, so college_id shoudn’t exist yet. Or
- by selecting “Lookup Wizard” as “Data Type” for college_id.
The wizard is self-explanatory. When selecting the column in this wizard, make sure you select BOTH the id, AND the columns that you want to be visible. You also have an option to hide the ID, which can be enabled if you prefer.
After finishing the wizard, you’ll see that you can now use informative fields instead of non-informative id’s, while transparently maintaining a foreign key relation using the non-informative id’s.
Manual solution
If you don’t to use the Lookup wizards, you can do it manually yourself in the field properties of college_id:
- change the “Display Control” to “List Box” or “Combo Box”
- set “Row Source Type” to “Table/Query”
- set “Row Source” to a query that contain BOTH the foreign key (college_id) AND the data that you want to be visible as value (cname in this case). The id column MUST be the first column, even if you don’t want it to be visible.
- set “Column Count” to the number of columns of the query from step 3
- set “Column Widths” for the columns. The ugly part: it is important that you set 0 as width for the id, so that it will be skipped and not be visible in the row, and thus the second column will be visible, which is our informative column.
Multiple informative field in the list
The text above only partially solves subproblem 2; it shows ONLY 1 informative field, even when you selected multiple informative field in the wizard.
For example, it shows
where only “MyFirstCollege” is visible instead of also the value “Rotterdam”. Instead we want:
where the “location” field of the COLLEGE table is also visible.
A manual solution to show multiple informative field in a row (like the last picture), is to concatenate the fields. This can be done by going to the field properties of college_id in the STUDENT table, and alter the SQL query:
SELECT COLLEGE.ID, COLLEGE.cname, COLLEGE.location FROM COLLEGE;
to
SELECT COLLEGE.ID, COLLEGE.cname & ‘-’ & COLLEGE.location FROM COLLEGE;