Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Normalize your data using the Table Analyzer

When you import data into Access, 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).

Two tables created from one table and the query that brings their data back together

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×