Ms Office Tune Up

So far, you’ve seen how relationships make it easier to review and edit your records. But what about when you add your records in the first place? Relationships are usually based on an unhelpful AutoNumber value. When you create a new doll, you probably won’t know that 3408 stands for Bobelle House O’ Dolls. Access stops you from entering a manufacturer ID that isn’t linked to anyone at all, but it doesn’t help you choose the ID value you want.

Fortunately, Access has a technique to help you out. In the previous chapter, you learned about lookups (Section 4.4), a feature that provides you with a list of possible values for a column. When creating a lookup, you can supply a list of fixed values, or you can pull values from another table. You could create a lookup for the ManufacturerID field in the Dolls table that uses a list of ID values drawn from the Manufacturers table. This type of lookup helps a bitit gives you a list of all the possible values you can usebut it still doesn’t solve the central problem. Namely, the befuddled people using your database won’t have a clue what ID belongs to what manufacturer. You still need a way to show the manufacturer name in the lookup list.

Figure 5-10. There are two relationships at work here. Customers is the parent of Orders (which lists all the orders a customer’s placed). Orders is the parent of OrderDetails (which lists the individual items in each order). By digging through the levels, you can see what each customer bought.

Happily, lookup lists provide just this feature. The trick is to create a lookup that has more than one column. One column holds the information (in this case, the manufacturer name) that you want to display to the person using the database. The other column has the data you want to use when a value’s picked (in this case, the manufacturer ID).

The following steps show how you can create a lookup list that links the Dolls and Manufacturers tables:

1. Open the child table in Design view.

   In this example, it’s the Dolls table.

2. Select the field that links to the parent table, and, in the Data Type column, choose the Lookup Wizard option.

   In this example, the field you want is ManufacturerID.

3. Choose “I want the lookup column to look up the values in a table or query” and then click Next.

   The next step shows a list of all the tables in your database, except the current table.

4. Choose the parent table, and then click Next.

   In this case, you’re after the Manufacturers table. Once you select it and move to the next step, you’ll see a list of all the fields in the table.

5. Add the field you use for the link and another more descriptive field to the list of Selected Fields (Figure 5-11). Click Next to continue.

   In this case, you need to add the ID field and the Manufacturer field.

   ---

   Tip: In some cases, you might want to use more than one field with descriptive information. For example, you might grab both a FirstName and LastName field from a FamilyRelatives table. But don’t add too much information, or the lookup list will become really wide in order to fit it all in. This looks a bit bizarre.
   

   ---

6. Choose a field to use for sorting the lookup list (Figure 5-12), and then click Next.

   In this example, the Manufacturer field’s the best choice to sort the list.

   
   Figure 5-11. The secret to a good lookup is getting two pieces of information: the primary key (in this case, the ID field) and a more descriptive value (in this case, the manufacturer’s name). The ID field’s the piece of information you need to store in the doll record, while the Manufacturer field’s the value you’ll show in the lookup list to make it easier to choose the right manufacturer.

   
   Figure 5-12. It’s important to sort the lookup list, so that the person using it can find the right item quickly. One links students to classes, and the other links teachers to classes.

7. The next step shows a preview of your lookup list (Figure 5-13). Make sure the “Hide key column” option’s selected, and then click Next.

   Although the primary-key field has the value that links the two tables together, it doesn’t mean much to the person using the database. The other, descriptive field’s more important.

   
   Figure 5-13. Here, the lookup list shows the manufacturer name (the Manufacturer field) and hides the manufacturer ID (the ID field).

8. Choose a name for the lookup column.

   Usually, it’s clearest if you keep the name of the field that uses the lookup (in this case, ManufacturerID).

   The final step also gives you an option named Allow Multiple Values. If you check this, then the lookup list shows a checkbox next to each item, so that you can pick several at once. (In this example, you can create a doll that has more than one manufacturer.)

9. Click Finish.

   Now, Access creates the lookup for the field and prompts you to save the table. Once you do, Access creates a relationship between the two tables you’ve linked with your lookup column. Here, Access creates a parent-child relationship between Manufacturers and Dolls, just as you did yourself in Section 5.2.

Now, if you switch to the design view of the Dolls table, you can use your lookup when you’re editing or adding records (Figure 5-14).

Figure 5-14. Even though the Dolls table stores an ID value in the ManufacturerID field behind the scenes, that’s not how it appears on your datasheet. Instead, you see the related manufacturer name. Even better, if you need to add a new record or change the manufacturer that’s assigned to an existing one, then you can pick the manufacturer from the list by name.

Popularity: 3% [?]