Using the Table Analyzer Wizard

Access 2003's Table Analyzer Wizard detects cells containing repeated data in table columns and proposes to create two new related tables to eliminate the repetition. This wizard uses the Lookup Wizard, described in Chapter 11 to create the relationship between the two new tables. After the Wizard creates the new related tables, NewName and Lookup, your original table is renamed to TableName_OLD, and the Wizard creates a one-to-many INNER JOIN query named TableName to return a result set that duplicates the Datasheet view of the original table. So, you need not change the references to TableName in your Access application objects.

The Lookup table must have a valid primary-key field to provide unambiguous association of a single record in the Lookup table with a foreign key field in the NewName table. One of the problems associated with repetitious data is data-entry errors, such as occasional misspelling of a company name or an address element in Lookup. The Table Analyzer Wizard detects and displays instances of minor mismatches in repeated cell values, such as a missing apostrophe, for correction. If such errors aren't corrected, the Lookup table includes spurious, almost-duplicate entries that violate the rules of table normalization.

Northwind.mdb's Orders table has a set of fields for shipping addresses. The data in these fields is the same for every order placed by each customer with three exceptions: order numbers 10248, 10249, and 10260. Shipping addresses comprise the bulk of the data in the Orders table, so removal of duplicate shipping information greatly reduces the size of the Orders table. Placing shipping addresses in a lookup table also offers the opportunity to streamline the data-entry process.

Note

The Access 97 and 2000 versions of the Table Analyzer Wizard had several bugs that resulted in error messages or spurious typographical error entries. The Access 2003 version corrects the problems.

To demonstrate use of the Table Analyzer Wizard to eliminate duplicate shipping address information in the Orders table of Northwind.mdb, follow these steps:

1. Use the Clipboard method described in the preceding section to create a copy of the Orders table named SalesOrders in the Northwind.mdb database. Working with a copy prevents making changes to Northwind.mdb's sample tables that would affect later examples in this book.

2. Launch the Table Analyzer Wizard by choosing Tools, Analyze, Table.

3. Skip the two introductory dialogs by clicking the Next button twice to reach the Table Selection dialog shown in Figure 5.44.

   Figure 5.44. Select the table to analyze in the third Table Analyzer Wizard dialog.

   

4. Select the table with the duplicated data in the Tables list box, the SalesOrders table for this example, and clear the Show Introductory Pages? check box. Click Next to continue.

5. You want to choose the fields for the Lookup table, so select the No, I Want To Decide option, and click Next. The Wizard displays a list of fields in the SalesOrders table renamed to Table1.

6. Click to select in the Table1 field list the first of the fields with duplicated information, ShipName; then press Shift and click the last of the fields to move, ShipCountry (see Figure 5.45).

   Figure 5.45. Select the fields with the duplicate data to move to a new lookup table. For this example, the fields to select begin with "Ship."

   

7. Holding the left mouse button down, drag the selected fields from the field list to an empty area to the right of the Table1 list. When you release the mouse button, the wizard creates a new field list for proposed Table1 with a many-to-one relationship between Table1 and Table2. The relationship is based on a lookup field in Table1 and a Generated Unique ID (AutoNumber) field in Table2. An input box opens to rename Table1; type ShipAddresses in the Table Name text box (see Figure 5.46). Click OK to close the input box.

   Figure 5.46. The Wizard designs a lookup table to contain the fields moved from the source table, and opens an input box in which you assign a name to the lookup table.

   

8. CustomerID is a better choice than an AutoNumber field for the initial primary-key field of ShipAddresses, because there's currently only one correct ShipAddress per customer in the Orders table. Click and drag the CustomerID field from the Table1 field list to the ShipAddresses field list. With the CustomerID field selected in the ShipAddresses field list, click the Set Unique Identifier button (the one with the key icon only). The Generated Unique ID field disappears and the CustomerID field becomes the primary key for the proposed ShipAddress table (see Figure 5.47). Click Next to continue.

   Figure 5.47. Specify the CustomerID field as the primary key for the ShipAddresses lookup table.

   

9. If the Wizard detects a misspelling of an entry in the lookup table, it opens a Correcting Typographical Errors...dialog. The Wizard bases the value in the Correction column on the frequency of exact duplication of records. In this case, the Wizard has detected two ShipAddress values for Old World Delicatessen (see Figure 5.48). Click the Next Key >>> button.

   Figure 5.48. The Wizard detects and proposes to correct a misspelled shipping address, which actually reflects a second shipping address. Two additional records have different shipping addresses, which call for shipment to a destination other than the customer's billing address.

   

10. Click the Next Key >>> button three times to view the three additional records (10260 for OLDWO, 10249 for TRADH, and 10248 for WILMK) with different shipping addresses. Shipping information in the three records later reverts to the values selected in the check boxes, because the Wizard can't handle multiple shipping addresses for a single primary key value.

11. Click Next and the Wizard proposes to create a query, in this case named SalesOrders, that substitutes for the original SalesOrders table. Accept the default Yes, Create the Query option. Clear the Display Help check box to prevent two wizard Help screens from appearing when you complete the operation.

12. Click Finish to create the SalesOrders query, and acknowledge the "Here is the query..." message to open the SalesOrders query.

13. Select the Lookup to ShipAddresses field and click the down-arrow button to open the lookup list, which displays the shipping addresses extracted from the SalesOrders table (see Figure 5.49). Notice that the list has only one entry for ALKFI, proving that the wizard corrected the spelling error.

    Figure 5.49. The SalesOrders query, which replaces the SalesOrders table, has a lookup field from which you can select a shipping address for the order.

    

The Wizard has renamed the original SalesOrders table as SalesOrders_OLD, and substitutes the SalesOrders query for the SalesOrders table. The Nwind05.mdb database in the \Seua10\Chaptr05 folder of the accompanying CD-ROM includes the tables and query created in the preceding steps.

Tip

Extracting the duplicate shipping address information from the copy of the Orders table to a new ShipAddresses table is helpful to demonstrate use of the Table Analyzer Wizard. But the preceding example isn't practical in the real world, where individual customers might have several shipping addresses. You're likely to find this wizard better suited for extracting duplicate information from spreadsheets you import into Access tables than from existing relational tables.

To make the ShipAddresses table useful, you must add a field, such as ShipToID, to identify multiple shipping addresses for a single customer. Assign a value of 0 for the ShipToID field for the default shipping information created by the wizard. Additional shipping addresses for a particular CustomerID are numbered 1, 2, 3, .... You need to redesign forms that specify shipping addresses to allow adding new ShipAddresses records for customers. You must change the primary key to a composite primary key consisting of CustomerID + ShipToID, and you must use VBA code to create successive ShipToID values automatically for a particular CustomerID.

Note

If you want to return Northwind.mdb to its original state, open the Database window, and delete the SalesOrders query plus the SalesOrders_OLD, Table1, and ShipAddress tables.