Understanding Lookup Fields

Let's begin discussing tables by introducing lookup fields. This might seem an odd, even perverse, place to start. Lookup fields are simply one Access featurea minor one, at that, and certainly not crucial to table creation. Indeed, my own bias is against using them.

But just as the diaries of obscure priests can illuminate profound religious controversies, an understanding of lookup fields is a superb way to introduce table creation in an Access database. Equally important, the topic is an excellent vehicle to review and reinforce the database principles you have learned thus far. The explanation might at first seem just a rehash of Chapters 2 and 3, but ultimately you will find it worthwhile. Nevertheless, if you feel at this point that you've seen one too many one-to-many relationships, and if you feel you have the material in the first four chapters down cold, feel free to jump to the next major section, titled "Creating Access Tables."

The Northwind sample database in Access makes significant use of lookup fields. I start by examining two tables there and comparing them to similar ones in another database that doesn't use lookup fields.

When Is It Okay to Repeat Values?

It will be faster and more effective if I describe an example that shows how lookup fields work instead of asking you to do the exercise yourself (which I usually prefer to do). Feel free to follow along by opening and using the Northwind.mdb database. I strongly suggest that you make and use a copy of the Northwind file, which can likely be found in your Program Files/Microsoft Office/..../Samples folder, rather than the original file, which is available by selecting Help, Sample Databases, Northwind Sample Database. (I give you an exercise to do in Northwind in a few pages, so you might as well make a copy of the file right now.)

The Northwind database contains the Products table (see Figure 5.1). It has data about the subject productsproduct names, the supplier, the category, and so on. The table has only one field that has anything to do with the supplier; it contains the supplier name. There is no contact information for the supplier; the contact name, e-mail address, and telephone number are all absent.

Figure 5.1. The Products table in Datasheet view.

I've stated that each table in a relational database should have only one subject, and that all the fields in the table should contain data only about that subject. It's easy to assert such abstract principles definitively, but it's far more difficult to put them into practice. How does the "one subject" rule apply in this example? Shouldn't supplier contact info be in a products table? What could be more basic, more critical, more "part of" product data than values that tell you how to get in touch with the people who supply them?

But the issue is not whether a supplier's telephone number lies somewhere within the universe of product data. It certainly does. It's whether the phone number is much more closely associated with another subject, and thus much better included in another tablenamely, Suppliers.

Let's take a closer look at the consequences of including supplier contact info in the Products table. Northwind contains a separate Suppliers table (see Figure 5.2), which contains contact information such as address, phone number, and home page. The first record is for Exotic Liquids; the contact is Charlotte Cooper. In the Products table, ProductIDs 1, 2, and 3chai, chang, and aniseed syrupare all provided by this same supplier, Exotic Liquids. Imagine if you had to store contact info about this supplier for each product. You'd have to enter the telephone numberas well as the contact name, contact title, street address, and so onagain and again for Exotic Liquids. And that would be true for all other suppliers as well.

Figure 5.2. The Suppliers table in Datasheet view.

Q&A

Q1:

I still don't understand what the problem is with repeating data in a table, or when it's okay to repeat it and when it isn't. As you point out, the company Exotic Liquids is repeated at least three times in the Products table. So why is that repetition okay? In the same table, categories are repeated, unit prices are repeated, and reorder levels are repeated. What's the big deal about repeating stuff?

A1:

Each record of the Products table uniquely identifies the supplier for that product. When you enter Exotic Liquids as the supplier for the products chai, chang, and aniseed syrup, in each case you've told Access which company supplies the product. Nowhere else in the database is that data entered, and nowhere else is it required. In other words, the data is entered only once. But to also include Northwind's contact at Exotic Liquids (namely, Charlotte Cooper) for each record would be redundant. Because you have the contact name for Exotic Liquids in its supplier record, you know that if you need to call the company, you want to speak to Charlotte Cooper. It doesn't matter whether the product you want to talk about is chai, chang, aniseed syrup, or any other product Exotic Liquids supplies. (The same company could conceivably have different salespeople for different products, but let's keep it simple.) The contact name Charlotte Cooper is already in the Suppliers table, so there's no need to repeat it ad infinitumindeed, no need to include it at allin the Products table. The key is establishing a relationship between the Products and Suppliers tables, as I'll discuss next.

Why Is There Text in a Number Field?

Now, finally, let's look at a facet of the problem you haven't seen in your previous work. In Design view of the Suppliers table, the first row is SupplierID (see Figure 5.3). It has the AutoNumber data type. This field is the table's primary key and uniquely identifies each supplier in the Suppliers table. Each supplier in the Suppliers table has a different SupplierID, and each supplier has one and only one SupplierID.

Figure 5.3. The Suppliers table in Design view.

In Design view of the Products table, the top pane includes the SupplierID field. (As I'll explain a little later, the field's Caption property is Supplier; so in the table's Datasheet view, the column heading is not SupplierID, but Supplier, as shown in Figure 5.1. Don't let this bother you: This is purely for cosmetics and does not affect any of the following discussion.) The SupplierID field in the Products table has the Number data type (see Figure 5.4), which means that only numbers can be entered in the field. The Description tells you that the value for this field should be the same entry as it exists in the Suppliers table.

Figure 5.4. The SupplierID has a Number data type and includes matching data from the Suppliers table.

In fact, a one-to-many relationship has been established between the Products and Suppliers tables through the SupplierID field. (You can verify this by viewing the Relationships window, which is available by selecting Tools, Relationships.) In the Products table, SupplierID is a foreign key. Whereas a supplier can appear only once in the Suppliers table (for example, there is only one record for Exotic Liquids), it can appear many times in the Products table (you've already seen three instances of Exotic Liquids).

The data type of the Supplier field is a number, and the Description says it's the same number as the SupplierID in the Suppliers table. That means the first three records for the Supplier column in the Products table should be 1, the SupplierID for Exotic Liquids. Instead, however, the datasheet shows the actual name of the company (see Figure 5.5). Why? And how can a field have a Number data type if it displays text?

Figure 5.5. A small section of the Products table in Figure 5.1. The SupplierID field, which has the column heading Supplier, displays the name of the supplier (Exotic Liquids), not its ID number (1).

The Advantage of Lookup Fields

The answer is that SupplierID in the Products table is a lookup field. Using the matching SupplierID, the field "looks up" the company name in the Suppliers table and replaces the ID with the actual name. The field still stores the SupplierID in the Suppliers table, but it now displays the supplier name.

This would seem to present the best of all possible worlds. The field contains that unique ID that enables you to combine all the data from the Suppliers table with that of the Products table in forms, queries, and reports. But instead of a meaningless ID, it displays a value that has meaning that the user can understand.

Let's take a look at what happens when you don't use a lookup field. Download the NiftyLionsChap5.mdb database from the companion web site (www.awprofessional.com/title/0321245458) to a convenient folder and open it. This imaginary database is for Nifty Lions, a small mail-order firm that sells various products with lion motifs to the general public. It has many (but not all) of the same tables that are in Northwind, along with similar table structures. It has far fewer records and shorter names, however, so I hope it will be easier to work with.

NOTE

I've used the Leszynski naming convention to name the main Access objects. Thus, a Customers table is titled tblCustomers, and a Merchandise table is called tblMerchandise, instead of simply Customers and Merchandise. The Leszynski system is described a little later in a sidebar. It's not a big deal: I've used it mainly because you're likely to see it in other contexts.

NiftyLions has a Suppliers table. As in Northwind, you have a SupplierID in the first column and the supplier company name in the second. NiftyLions also has a Merchandise table. As in Northwind's Products table, you have Product Name and Supplier fields (see Figure 5.6). Unlike Northwind, however, the Supplier field shows the actual SupplierID, not the supplier name. Thus, the foreign key in the Merchandise table both stores and displays the supplier's AutoNumber primary key, which makes all the values for that supplier in the suppliers table available to the Merchandise table.

Figure 5.6. The Supplier field in the Merchandise table is a number.

Figure 5.7 shows the Merchandise table in Design view, with the cursor in the SupplierID field. In the Field Properties section below, the Caption property is Supplier. SupplierID and Supplier are the exact same field; in the datasheet, however, a caption has been used to make it easier to read.

Figure 5.7. In Design view of the NiftyLions Merchandise table, the SupplierID is a number. But no lookup field has been created, so in Datasheet view, you see the actual SupplierID, not the supplier name.

In the Description, a note has been added that the SupplierID field is the same as that in the Suppliers table. In the Merchandise table, SupplierID is a foreign key. Many products can have the same supplier, while in the Suppliers table the supplier's record can appear only once. But no lookup field has been created. Thus, the actual ID of the supplier is displayed in the Merchandise table.

That's going to be pretty confusing unless you happen to remember the actual name of SupplierID 1and ID 2, and ID 3, and every other ID. It might not be a problem with seven suppliers, but suppose you had 190 shippers (or 1,000 products, or 10,000 namesthe same principle applies for other fields). Thus, you can see the advantages the lookup field offers.

The Downside of Lookup Fields

You can readily see the problem with lookup fields by creating a query using the Products table of the Northwind database. Let's create a query that will include only products supplied by Exotic Liquids. (If you need a refresher on simple queries, take a look at Chapter 8, "Queries," before proceeding.) If you haven't already done so, find the Northwind.mdb database on your hard drive in the Program Files/Microsoft Office/..../Samples folder; make a copy of the file and open it.

1.	In the Northwind database window, click the Products table and choose Insert, Query; then click OK.
2.	Double-click the field list title to select all the fields.
3.	Drag and drop all the fields on the design grid.
4.	In the Criteria row of the SupplierID field, type exotic liquids. You usually don't need to capitalize criteria.
5.	Click the View button in the upper-left corner to see your records. You have an error message stating that you have a data type mismatch in the criteria expression (see Figure 5.8). You entered the expression exotic liquids. But remember, SupplierID in the Products table doesn't have a Text data type; it has a Number data type. You have to enter the number of the supplier to see your records. Figure 5.8. When you run the query, you get a data type mismatch error message.
6.	Click OK in the error message.
7.	In the SupplierID column, edit the criteria to 1. The SupplierID of Exotic Liquids is 1.
8.	Click View to see your records. The Supplier field shows all the records with Exotic Liquids as supplier.
9.	Close the query. You can save the query, if you want, or discard it.

In almost every way that actually mattersstructure, capability to integrate data, the actual value stored in the fieldthe Supplier field in the Northwind products table and the Supplier field in NiftyLions's Merchandise table have the same qualities. Notably, they are both foreign keys on the "many" side of a one-to-many relationship. In only one aspect, the value displayed, do the two fields differ. To the untrained eye, however, the two fields seem quite distinct.

Are there ways to construct this query so you can use supplier names instead of ID numbers as criteria? Certainly. But here's the problem with lookup fields: Based on the datasheet in the underlying table, you had every reason to believe that you could use supplier names as criteria in the SupplierID field. When you try that, however, you get an error message that contains no instructions. You're left scratching your head about what to do next. The lookup field masks the underlying structure of your database.

Q&A

Q1:	Okay, I see the issue you describe, but not using lookup fields seems an even bigger problem. How am I supposed to enter suppliers in a Products tableor, much more important for me, customers in an Orders tableif I have to remember all those ID numbers?
A1:	You don't have to remember them because you're not going to use tables to enter data. You're going to enter values in forms. As you'll see when I discuss creating forms, you'll simply be able to select the name of the supplier, customer, shipper, and so on instead of worrying about ID numbers.

Creating Lookup Fields

Because I'm not enthusiastic about lookup fields, I'm not going to go through an example on how to create them. Suffice it to say that, in Table Design view, you begin by choosing the Lookup Wizard from the Data Type drop-down list for the field.

Q&A

Q1:	Even if I don't use lookup fields, maybe someone in my office likes them. How can I tell if a lookup field has been created in an Access table?
A1:	When you click in a field row in Design view, there is both a General tab and a Lookup tab in the lower pane (see Figure 5.7). If a lookup field has been created and is being used, the Display Control property on the Lookup tab will be Combo Box, and there will be a host of associated properties. If no lookup field has been created, the Display Control property will simply read Text Box.

What Lookup Fields Can Teach You

Although lookup fields themselves might be suspect, the importance of the principle on which they are based cannot be overstated. The lookup fieldand the display of the supplier name instead of the SupplierIDwas possible only because a one-to-many relationship existed between the two tables. Again and again in Access, you will find yourself bringing together data from different tables by establishing relationships. You will be able to do that because the tables are related to one another. As you have seen, those associations are made possible because the tables have matching data in one field. Whether you're designing multitable queries, adding combo boxes to forms, or including fields from different tables in the Report Wizard, your work rests on this same bedrock rule. That might not be apparent now, but it will be increasingly obvious as you proceed through this book and learn more about Access.

Concluding Lookup Fields

Years ago, I worked with a group of writers in Japan where we often "polished" (read: did a complete rewrite of) Japanese-to-English translations. Translating is more art than science, and translators have much latitude in their work. Many translators (mostly American, but Japanese as well) spent a lot of effort making their English sound as good as possible. But for those of us who had to rewrite the translations anyway, we wanted them to be as pure and "Japanesey" as possibleeven if they were ungrammatical. It wasn't that we writers were afraid of being made redundant. It was that we wanted to get a true feel of what the Japanese said.

Perhaps that's not the best analogy, but that gives you a sense of how I feel about Access tables. I don't want little devices in them, such as lookup fields, that supposedly make my life easier. I want to see the values that have actually, truly been stored. If my tables are sound, I can create the queries, forms, and reports I needand be confident that they are supported in the underlying tables.

Meaningful Primary Keys

A serious charge could be made against my presentation on lookup fields, which runs something like this: Hey, the real problem is those AutoNumber primary keys you and Northwind are using. Why use a primary key with no meaning whatsoever? If you used a key that truly identifies the fieldif you could take one look at the key and know who the supplier isyour problem would be solved. The primary key itself tells you who the supplier is, and there's no need for lookup fields.

I've discussed natural and artificial keys at some length in previous chapters, so I won't debate the issue here again. You might decide to develop and use meaningful primary keys in some or all of your tables, but I still demur. Creat ing meaningful but unique primary keys is not easy. Few things in life are both meaningful and singular. Even seemingly unique situations can turn out to be not so singular.

Let me just reiterate: The most important thing is that your table organization and structure are solid. If it is, whether you use meaningful prime keys or not, you can create the queries, forms, and reports you need that show the information you want.