Using the Table Analyzer Wizard

Even if you use good design techniques (see Article 1, “Designing Your Database Application” on the companion CD) and build a normalized database, you might not arrive at the best design. In fact, you often cannot fully evaluate a database design until you use the database and store data. Access 2007 includes the Table Analyzer Wizard that can examine data in your tables (or data you import from another source) and recommend additional refinements and enhancements to your database design.

One of the key elements of good database design is the elimination of redundant data. The Table Analyzer Wizard is particularly good at scanning data in your tables, identifying data repeated in one or more columns, and recommending alterations to your design that break out the redundant data into separate tables. You can find an example of such redundant data in the Conrad Systems Contacts database (Contacts.accdb). Imagine that a customer sent you a file containing company and contact information. Sounds like a good, easy place to start collecting or adding to your contact data. However, when you open the file, you see that most companies are listed several times because the original data isn’t normalized. You’ll find just such a table, saved as tblContacts4TableAnalyzer, in the Conrad Systems Contacts sample database.

You can see how the Table Analyzer Wizard works by using it on the tblContacts4TableAnalyzer table. First, open the Conrad Systems Contacts database. Click the Database Tools tab and then click the Analyze Table command in the Analyze group. Access starts the Table Analyzer Wizard and displays the first page, shown in Figure 5–27.

Figure 5–27: The opening page of the Table Analyzer Wizard informs you about the problems it is designed to correct.

This first page is one of two introductory pages that explain what the wizard can do. Click the Show Me An Example buttons to get a better understanding of the kinds of problems the wizard can solve and to see how the wizard works. Click Next twice to get to the first “action” page in the wizard, shown in Figure 5–28.

Figure 5–28: Select the table you want to analyze in the Table Analyzer Wizard.

On this page, you select the table you want to analyze. For this exercise, select the tblContacts4TableAnalyzer table. (Note that you have a check box on this page to continue to show the two introductory pages each time you start the wizard. If you think you understand how the wizard works, you can clear the check box to skip the introductory pages the next time you start the wizard.) Click Next.

On the next page, the wizard asks if you want to rearrange the fields in the target table or if you want the wizard to decide the arrangement for you. If you know which fields contain redundant data, you can make the decision yourself. Because the wizard handles all the “grunt work” of splitting out lookup data, you might choose the latter option in the future to further normalize tables in your application. For now, select the Yes, Let The Wizard Decide option to see how effective it is. Click Next to start the analysis of your table. Figure 5–29 shows the result of the wizard’s analysis. (We’ve shifted the contents of this figure to fit the result in a single window.)

Figure 5–29: The Table Analyzer Wizard examines the data in your table and makes an initial recommendation.

In this case, the wizard did a pretty good job of identifying the separate company and contact information and splitting the fields into two tables. It also recognized that ContactType and Department have lots of repeating values and perhaps should both be in separate lookup tables. There isn’t enough data (only 18 rows-and each contact is related to only one company) in the table for the wizard to have noticed a many-tomany relationship between companies and contacts. It probably kept the HomeStateOrProvince field with the company data because it didn’t see a different value across multiple rows for the same company.

We really don’t need to do much work to fix this if we’re happy with the one-to-many relationships. First, click on HomeStateOrProvince in Table 2 and drag and drop it into Table1 between HomeCity and HomePostalCode. Also move the Lookup To Table3 field from Table2 to Table1 to correctly relate the contact type lookup information to contacts instead of companies, and move the Department field from Table4 to Table2 between CompanyName and Address. (The last move should remove Table4 from the design window.)

After you have adjusted the way the wizard split your tables, the next step is to give each of the new tables a new name. To rename a table, first click the table name and then click the Rename Table button in the upper part of the window. (You can also double-click the table’s title bar.) The wizard opens a dialog box in which you can enter a new name. You should change Table1 to Contacts, Table 2 to Companies, and Table 3 to ContactTypes. Click Next when you are finished.

The next page asks you to verify the primary key fields for these tables. You can select new fields for the primary key of each table or add fields to the primary key. The wizard couldn’t identify any naturally occurring unique value, so it generated a unique ID (which will be an AutoNumber in the final tables) for two of the tables. You need to select the Contacts table and click the Add Generated Key button to create a primary key for that table. Figure 5–30 shows the result of moving fields, assigning new names to the tables, and adding a primary key. Click Next to accept the settings and go on to an analysis of duplicate values in the lookup tables.

Figure 5–30: After adjusting what the wizard proposed, you’re ready to create the new tables.

The Table Analyzer Wizard looks at values in the new tables to try to eliminate any possible duplicates created by typing errors. Figure 5–31 shows the result of this analysis on the sample table. Because the wizard sees several rows with Marketing or Sales in them, it suggests that some of these values might, in fact, be the same. You can use this page to tell the wizard any correct values for actual mistyped duplicates. This could be extremely useful if your incoming data had the same company listed several times but with a slightly different spelling or address. The wizard will store only unique values in the final table. You could, if necessary, tell the wizard to substitute one set of similar values for another to eliminate the near duplicates. In this case, you should tell the wizard to use the original value for all the values listed as duplicates by clicking the arrow in the Correction field and selecting the (Leave As Is) option as shown in Figure 5–31. Click Next when you are finished to go on to the next page.

Figure 5–31: The Table Analyzer Wizard gives you the opportunity to fix potentially duplicate lookup values.

Finally, the wizard offers to create a new query that has the same name as the original table. (See Figure 5–32.) If you’ve already been using the old table in queries, forms, and reports, creating a new query that integrates the new tables into the original data structure means you won’t have to change any other objects in your database. In most cases, the new query will look and operate just like the original table. Old queries, forms, and reports based on the original table will now use the new query and won’t know the difference.

Figure 5–32: The final page of the Table Analyzer Wizard lets you decide whether you want a query to duplicate the original unnormalized data structure.

This is only an example, so select No, Don’t Create The Query. Click Finish to build your new tables. The wizard also creates relationships among the new tables to make sure you can easily re-create the original data structure in queries. Figure 5–33 shows the three new tables built by the wizard.

Figure 5–33: The Table Analyzer Wizard automatically separates your old data into the new table structure.

Notice that the wizard left behind an ID field in the Contacts table as a link to the ContactTypes table. The values in the ContactTypes table are actually unique, so there’s no reason not to use the actual value as the primary key instead of an artificial ID. We’ll show you how to change the primary key later in this chapter.