Database Reference
In-Depth Information
If you ask a dozen sales clerks to enter the name of a specific customer, product, and ship-
per in an invoice, it is unlikely that all of them will enter the same thing. In cases like this,
in which the number of correct choices is limited (to actual customer, actual product, and
actual shipper), providing the means to choose the correct information from a list derived
from the Customers table, the Products table, and the Shippers table will improve your
database's accuracy and consistency.
One of the key concerns when looking up information in another table is the efficiency of
the process. Looking up an employee in an Employees table with 9 records is not very diffi-
cult. Looking up a customer in a Customers table with 200 records, however, could be quite
tedious. If you use an intuitive CustomerID instead of relying on an autogenerated number
as the primary key of the Customers table, database users can enter the CustomerID and
then verify it in the list. For example, using the first three letters of a customer's last name
plus the first two of his or her first name will almost certainly result in unique CustomerID
values. These values will not only serve as the primary key for the Customers table but will
be easy for users to intuit when working in other tables linked to the Customers table.
6
In this exercise, you'll use the Lookup wizard to create a list of possible field values from the
entries in a field in a related table. You'll also change the primary key in a table to facilitate
the lookup process.
SET UP
You need the GardenCompany06 database you worked with in the preceding
exercise to complete this exercise. If necessary, open the database. Then follow the steps.
1
On the
Database Tools
tab, in the
Relationships
group, click the
Relationships
button. On the
Relationships
page, notice that there are relationships between the
Customers
and
Orders
tables and between the
Employees
and
Orders
tables.
We want to create Customer and Employee lookup fields in the Orders table, so first
let's delete the existing relationships and fields.
2
Right-click the diagonal part of the line between the
Customers
and
Orders
tables,
and click
Delete
, clicking
Yes
to confirm the deletion. Repeat this step for the line
between the
Employees
and
Orders
tables. Then close the
Relationships
page.
TIP
If you want to remove a table's box from the Relationships page, right-click the
box and click Hide Table. If you want to remove all the boxes, click the Clear Layout
button in the Tools group on the Design tool tab.
