MS Access 2003 - Lesson 39: Analyzing tables

TipsMake.com - Access includes a very powerful tool that you can use to analyze data in one or more tables . The purpose of the analysis is to determine if there is little redundancy in the data storage in the table. 'Less redundancy' here means that Access tries to remove any data that is stored in the table more than once. For example, the 'company name' field should be taken out of the original table and replaced with a new table. Although the result will be 2 tables (original and new tables), the database will be more efficient because the information is not duplicated in the original table.

An example is probably a good way to prove the analysis process. The example in this section is based on the database Accounts.mdb. This database contains a table called Invoices, which has over 1000 invoices in it. Some minimum fields are maintained in the table but there is quite a bit of redundancy in the data.

Begin the analysis

With the Accounts database, click your mouse on the Tables button in the Database window. Click on the Invoices table. Then, select Analyze from the Tools menu. Access displays a submenu from the table selection as shown below:

MS Access 2003 - Lesson 39: Analyzing tables Picture 1
Figure 1: The Table Analyzer Wizard dialog box.

The first on screen Wizard is the explanation of the purpose of the Table Analyzer Wizard. You can read all the information if you want, then click your mouse on the Next button when you are ready. The dialog box displays as follows:

MS Access 2003 - Lesson 39: Analyzing tables Picture 2
Figure 2: Select the table to analyze

Most databases can have more than one table. Fortunately, this database contains only one table named Invoices. Since this is the default table selected, click your mouse on the Next button. Access displays the following dialog box:

MS Access 2003 - Lesson 39: Analyzing tables Picture 3
Figure 3: Starting the analysis process

In this step you need to decide that you want to allow the Wizard to select the fields in the menu or you want to do it manually. Because the Wizard's job is quite easy, choose Yes, let the wizard decide . Even if the Wizard makes a bad choice, you can cancel the decision in the next step.

Confirm the separation of tables

When you are ready, click your mouse on the Next button. Access displays as follows:

MS Access 2003 - Lesson 39: Analyzing tables Picture 4
Figure 4: Verify the table separation

In this section, the Wizard shows you what it proposes to do. For the original Invoices table, the Wizard plans to divide it into 3 tables. Take a look at the fields suggested by the Wizard into each table, so it's easy to see that each table contains information about transaction invoices, customers, and products. Using this dialog, you can name the tables.

To see how to do this, make sure Table1 is selected (Figure 4). Then click your mouse on the Rename button (near the top right corner of the dialog box). Access displays a small dialog box that allows you to specify a new table name. For Table1, the name should be set to Invoice Transactions. Then, click your mouse on the OK button. Access updates the name as in the title bar for a table with a new name.

You should repeat this task to name Table2 and Table3 as Customers and Products. Click on the title bar of each table and then rename. When completed, the tables will appear as follows:

MS Access 2003 - Lesson 39: Analyzing tables Picture 5
Figure 5: Tables have been renamed.

Other things you can do in this dialog box (in addition to naming tables) are overwriting how the Wizard divides the table. When performing the analysis, you will want to make sure that each given table contains the relevant fields from the original table. In this example, the Wizard did very well, indicating that we have named the Customers table and need a unique CustomerID as well as CustomerName and Discount.

If you need to override the Wizard's division, all you need to do is click on a field name and drag it from one table to another. However, in this example it is not necessary to do so.

Confirm the primary key

When you click your mouse on the Next button, Access displays the next step as shown below:

MS Access 2003 - Lesson 39: Analyzing tables Picture 6
Figure 6: Set up the primary key

This dialog box is similar to the dialog box used in the previous section. However, different controls on the top of the dialog box. The wizard will ask to confirm each table must have a primary key. The primary keys for the Customers and Products tables are created by the Wizard. However, the Wizard forgets that the ID field will be a primary key in the Invoice Transactions table.

To specify the ID field as a key, first click on the field name. Then click on the tool that indicates the lock. Access responds by placing a small key to the left of the ID field in the Invoice Transaction table.

Correcting Typographical error

When you click your mouse on the Next button, the Wizard will analyze the data in the suggested table and display what might be considered an error. Access does this for each new table created by the Wizard. Because two new tables (Customerds and Products) are created by the Wizard in this example, this means Access displays two typographical correction dialogs. The first dialog for the Customer table is as follows:

MS Access 2003 - Lesson 39: Analyzing tables Picture 7
Figure 7: Correcting typographical error for Customers table

This is the dialog box that displays 5 customer names that may indicate errors. It gives this result by using an internal algorithm. If the spelling of the data matches the data in the table, it is said to be a potential error. All you need to do is use the pull-down list in the Correction column to select the correct spelling or setting (Leave As Is).

In this example table Customers, there are no spelling errors. You should change the Corrections column with all the records (Leave As Is) that the setting is selected for. When done, click your mouse on the Next button. The wizard then displays the Products panel as shown in Figure 8.

MS Access 2003 - Lesson 39: Analyzing tables Picture 8
Figure 8: Correcting typographical error for Customers table.

It only happens so when a record is identified as possible. Of course, in your own table there may be an error. You should go through the error correction process exactly as you would for the Customers table. In this case, you select the Leave As Is option for the Correction column.
Finish the analysis.

When you click your mouse on the Next button, the Wizard displays the final step as shown below:

MS Access 2003 - Lesson 39: Analyzing tables Picture 9
Figure 9: Final analysis step.

The final step in the Table Analysis Wizard is where you specify if you want to create a query that looks like the original table. If you don't want to create a trace, you can select No, don't create the query and click the Finish button to finish the analysis process. In case you want to create a query, it means that the Wizard creates a query that is a mixture of 3 tables, combined as the original table.

Click Yes, create the query and click the Finish button to complete the table analysis process. Access displays the new query on the screen as shown in Figure 10.

MS Access 2003 - Lesson 39: Analyzing tables Picture 10
Figure 10: New query created by Table Analysis Wiard.