Using the Table Analyzer to Create Relationships

At this point, are you completely satisfied with the design? Let's see whether the Table Analyzer agrees with you. This utility reviews current entries and makes suggestions on improving the design.

In a nutshell , the utility looks for repeated data and helps you copy that data to a new and related table. Not every table will need changes, but it doesn't hurt to run the analyzer just to see what it proposes. In addition, until you're more familiar with design rules, you can rely on the Table Analyzer to help you through the process of creating properly normalized tables. (Refer to Chapter 4 for a definition of normalization.) To analyze the plant table, follow these steps:

1. Select Analyze from the Tools menu and select Table from the resulting submenu.

2. The first pane in the wizard offers to show you a sample problem that you might experience with duplicate data. Feel free to review the example. Click Next when you're ready to continue.

3. The next pane offers to explain, by example, how the analyzer will split a table. View the example, and when you're ready to continue, click Next .

4. Select the table you want to analyzewe selected Plants. Click Next when you're ready to continue to the next step.

5. While learning, you'll probably want to let the wizard do as much work as possible. Let the wizard choose which fields to split into a new table by accepting the default option ( Yes, Let the Wizard Decide . Click Next to continue. We won't even look at the other option; if you knew how to split the table, you wouldn't need the wizard!

6. The next pane, shown in Figure 6.9, displays the wizard's suggestions for splitting the table. Does the solution look familiar? It might because this is exactly the route you took in Chapter 3, "Exploring the Database Window," when you designed the database. Right now, there's no relationship between the catalog and plant tables, so the wizard has no way of knowing that you already have a catalog table.

   Figure 6.9. The wizard offers its solution.

   

   At this point, you could abandon your task, but let's continue because you're probably unfamiliar with how this utility works. Just remember: You had the right design; all you're lacking is the relationship between the two tables.

7. Select Table1 and click the Rename Table button to the right of the window (refer to Figure 6.9). Enter the name PlantsNew , and then click OK .

8. Repeat step 7 and rename Table2 CatalogsNew . Then click Next to continue.

9. The next pane enables you to reset the suggested primary key. The wizard suggests adding an AutoNumber datatype field to the type table and using it as the primary key. However, the wizard doesn't define a primary key for PlantsNew, so you must do so. Select the CommonName field in the PlantsNew list and click the Set Unique Identifier button. In response, the wizard displays a primary key icon next to that field. Click Next to continue.

10. In the final pane, the wizard offers to create a query. You're not ready for queries yet, so select the No, Don't Create the Query option. You'll probably want to deselect the Display Help on Working with the New Tables or Queries options. Otherwise, you'll just have an additional window to close. Click Finish to complete the changes.

The two new tables, PlantsNew and CatalogsNew, are for the most part, just like Plants and Catalogs. The two fields in CatalogsNew are transposed, but that doesn't matter. In addition, PlantsNew has a second lookup field. Select any field in the CatalogName field to display that field's new lookup field, as shown in Figure 6.10. The Table Analyzer created it automatically.

Figure 6.10. The Table Analyzer has added a second lookup field to the plant table.

At this point, you have two relationships in your database:

• Between the TypeID fields in Plants and Types

• Between ID and CatalogsNew_ID (created by the Table Analyzer)

We completed this exercise just to show you how the analyzer works. Now you'll know what to expect when you need it. For now, delete both PlantsNew and CatalogsNew. To delete a table, simply select it in the Database window and then press the Delete key. After deleting the two tables the Table Analyzer created, just one relationship existsthe one between the Plants and Types table the Lookup Wizard created in the first section. It's okay to leave the new tables in the database if you want to study them later. They won't interfere with other objects.