Excel databases often contain repetitive information that is not normalized, making the data too unwieldy to move directly into an efficient Access database. But by using the Access Table Analyzer Wizard, your users can split the imported Excel table into several components and automatically create the relations needed between them. This tool reduces the need for data reentry, saving you and your users valuable time and resources.

What Table Analyzer does
Data can be imported into Access from any number of sources, including Excel. The Table Analyzer is designed to read a large table of data and convert it into an efficient database. This tool manages this task by looking for repetitive information in the table and determining which information might be better served by the use of a Lookup. In doing so, it creates a lookup field in the primary table and then moves the redundant data into a secondary table.

For example, imagine you have a table containing a listing of books. For each book, the section (fiction, nonfiction, etc.) is listed. Because the section name is repeated on many lines, this data is redundant. Better database design would put this information into a secondary table used as a lookup. This would allow the user to make changes to the section or section names without impacting each individual book record. The Table Analyzer will move the section names to that secondary table and automatically create the lookup needed for the primary table “Books.”

How to use the Table Analyzer
In this example, Table Analyzer Wizard will import some personnel information, which has redundant departmental information. The wizard will split the data into two tables using a lookup for the department information.

Step 1: Import the data from Excel
From the File menu within Access, click Get External Data | Import. Switch the file type to Excel and locate the original file you wish to import (see Figure A). Follow the wizard through the import process. This places all of your original Excel data into one “un”-normalized table in Access.

Figure A
This table shows the original data in Excel.

Step 2: Start the Table Analyzer
From the Tools menu, click Analyze | Table.

Step 3: Allow the wizard to suggest the new tables
After moving through the wizard’s introduction screens, select the table you wish to normalize and click Next (see Figure B). You will be given the option of performing the normalization manually or allowing the wizard to make suggestions. If you are new to the procedure, you should at least look at the suggestions the wizard makes. You can always modify the choices later.

Figure B
The wizard’s introduction screen explains the problems with duplicating information.

Step 4: Review the tables the wizard suggests
The next screen shows the tables and relationships suggested by the wizard. In this case, it moved the DeptCode (as a primary key), DeptSupervisor, and Building into a secondary table (see Figure C).

Figure C
The wizard has suggested these tables and relationships.

If you wish to move fields back to the primary table, simply drag the field name from one table to another. To create a new table not suggested by the wizard, drag the field name to a blank spot on the screen. A lookup field from the table where it originated will automatically be created.

Step 5: Name the new tables
Before continuing to the next step, you will want to rename the suggested tables. Click any field in the table and click the rename button in the upper right corner. Name the table and click OK.

Step 6: Make changes if necessary to primary key suggestions
Click next to move to the next screen and review the primary keys suggested by the wizard. Highlight the desired field and click the key button to designate it as the primary key. If you wish to add a new field for a system-generated primary key, click the key with the green plus sign.

Step 7: Create a query to match the original table if desired
The final step of the wizard allows you to have it automatically generate a query that matches the structure of the original table. This can be nice for users to view the data in the original format or for creating reports (see Figures D and E).

Figure D
The wizard will create a query that matches the original table structure.

Figure E
Retaining this data can be helpful in the long run.

Final result
The final result will be two tables containing the primary data and a lookup to the previously repeated information (see Figure F). You may wish to modify the structure of the lookup to show less information. By default, the wizard creates a lookup that uses all of the fields from the new table, which may be more information than your users require. The lookup properties can be modified in the Table Design View.

Figure F
You can modify the Lookup to fit your organization’s needs.

What the Table Analyzer doesn’t do
It’s important to keep in mind that the Table Analyzer will not be able to restructure all imported data correctly. It works best in those situations that require the creation of Lookup fields.

For instance, if you are simply trying to separate a table into multiple tables and create relationships between them, the Table Analyzer will often add extra IDs and lookups based on those IDs rather than use the existing primary key to establish the relationship.

In the example below, the Table Analyzer was used against a table containing payment and session information for registrants. In attempting to split the table into three separate tables (Registrants, Payment, and Sessions), the wizard created additional keys for “lookup” to the Payment and Sessions information (see Figure G). In a normalized relational database, the same ID key from the Registrants would have been used to relate information in the Payment and Sessions table. In this situation, the user would have been better off separating the data using Make Table and Append Queries to build the two related tables.

Figure G
This is an instance when the Table Analyzer would be ineffective.

Other tips for normalizing imported data

Do you know another way to build normalized data in a relational database from imported sources such as Excel? Share your tips by posting a comment or writing to Janice Ward.