Database Reference
In-Depth Information
Multicolumn lookup lists
If you want people to be able to select a friendly name from a list but you want the
database to store a different name or even a number, create a two-column lookup list
that associates the two types of entries. For example, you might want to associate em-
ployees' first names or nicknames with their employee ID numbers.
To set up a multicolumn lookup list:
1
Create a new lookup field, and indicate on the first page of the
Lookup
wizard
that you want to enter the values.
2
On the second page, change the
Number of columns
setting to
2
. Then enter
the data you want Access to store in
Col1
and the friendly name in
Col2
, and
click
Next
.
3
On the third page, designate the column in which the data to be stored is
located—in this case,
Col1
—and then click
Next
.
4
On the last page, assign a name to the field, select the
Limit To List
check box
if appropriate, and click
Finish
.
Clicking the field's arrow will then display a two-column list from which the user can
select an entry. The stored value will be displayed in the field.
To display only the friendly name in the list and in the table:
Switch to
Design
view.
1
In the
Field Properties
area, on the
Lookup
tab for the multicolumn field, change
the
Column Widths
property from
1”;1”
to
0;1”
.
2
Save the table.
3
Allowing only values in other tables
In “Defining relationships between tables” in Chapter 2, “Create databases and simple tables,”
you learned how to link tables in such a way that a user could not enter a CustomerID that
did not exist in the Customers table or an EmployeeID that did not exist in the Employees
table. These relationships are critical to ensuring that any specific item of data is stored in the
database only once. But relationships also provide a powerful means to improve the accu-
racy of the database's data.
