Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
After you import data, often the source data isn’t accurately divided into related tables. To maximise the benefit from Access, data must be normalised – split into different tables, each about one thing, that are linked by vital aspects of information. The Table Analyser can aid you with this pivotal task: on the ribbon, press Database Tools, and then in the Analyse group, choose Analyse Table. A wizard begins and guides 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 Analyser
3. Query created by the Table Analyser
4. Lookup list
If your Microsoft Access database includes a table with repeating information in one or more fields, use the Table Analyser to separate the data into related tables which allows you to store data more securely and efficiently. This process is called normalisation.
The Table Analyser segments one table that has repeating information into individual tables in which every kind of information is stored simply once. This enhances the database’s functionality, and shrinks its size. Once the wizard divides the data, you can still see and manage the data in one location by setting the wizard to make a query.
You can use the query to amend data from multiple tables simultaneously. The query even provides other efficient features to boost the accuracy of your data.
- If you update a field that is repeated in the query, all records associated with that value are instantly amended because you are genuinely updating only one field in the query’s underlying table.
- From the new tables, the query acquires Lookup fields, which enable you to change field values by selecting values from a list rather than actually rectifying a typed value. The Lookup list reviews values from the deeper table. After the same value applies to several records, you can guarantee accuracy and consistency by choosing that value from the list each time.
- Forms and reports that were formerly based on the initial table will automatically be related to the query now, because the wizard imports the original table’s name to the query and renames the table.
- Established forms and reports according to the query will adopt automatic field updates. New forms and reports will also gain Lookup lists.