Normalize your data using the Table Analyzer

When you import data, sometimes the source data isn't properly split out into related tables. To get the most benefit from Access, data needs to be normalized – separated into different tables, each about one thing, that are related by key pieces of information. The Table Analyzer can help you with this critical task: on the ribbon, click Database Tools, and then in the Analyze group, click Analyze Table. A wizard starts and walks you through the process.

Note:  The information in this topic applies only to a Microsoft Access desktop database (.mdb or .accdb).

1. Original table

2. Tables created by the Table Analyzer

3. Query created by the Table Analyzer

4. Lookup list

If your Microsoft Access database has a table that contains repeating information in one or more fields, use the Table Analyzer to split the data into related tables so that you can store data more safely and efficiently. This process is called normalization.

The Table Analyzer divides one table that contains repeating information into separate tables in which each type of information is stored only once. This makes the database more efficient and easier to update, and reduces its size. After the wizard divides the data, you can still view and work with the data in one place by having the wizard create a query.

You can use the query to update data from more than one table at the same time. The query also offers other timesaving features to increase the accuracy of your data.

• If you change a field that is repeated in the query, all records with that value are automatically updated because you are actually updating just one field in the query's underlying table.

• From the new tables, the query inherits Lookup fields, which let you change field values by picking values from a list instead of having to correctly type a value. The Lookup list looks up values from the underlying table. When the same value applies to more than one record, you can ensure accuracy and consistency by picking that value from the list each time.

• Forms and reports that were previously based on the original table will automatically be based on the query now, because the wizard assigns the original table's name to the query and renames the table.

• Existing forms and reports based on the query will inherit automatic field updates. New forms and reports will also inherit Lookup lists.