If you consider the average Excel spreadsheet, you will commonly see instances of repeated groups of data. What is happening here is that you are normalizing your data using the Access Table Analyzer.
In this tutorial:
Required knowledge:
1. Set up
the CSV file (it is delimited with semi-colons)
Import the data into a database table. Name the table tblImportedData. Once you have imported the data, you will observe that the data is not normalised.
You can see the repeated data clearly in the sample below (which is sorted by Country):
| ID | Country | Code | Pages | Hits | Bandwidth | Month |
|---|---|---|---|---|---|---|
| 411 | Albania | al | 0 | 6 | 166 | 9 |
| 298 | Algeria | dz | 2 | 31 | 967 | 7 |
| 500 | Angola | ao | 1 | 1 | 129 | 11 |
| 182 | Argentina | ar | 0 | 1 | 35 | 4 |
| 224 | Argentina | ar | 1 | 1 | 153 | 5 |
| 251 | Argentina | ar | 3 | 24 | 1780 | 6 |
| 312 | Argentina | ar | 0 | 7 | 724 | 7 |
| 359 | Argentina | ar | 0 | 11 | 824 | 8 |
| 10 | Australia | au | 10 | 39 | 1370 | 1 |
| 74 | Australia | au | 1 | 3 | 46 | 2 |
To get the most benefit from a relational database like Access, data needs to be separated into different tables, each about one thing, which are related by key pieces of information. We call this process normalizing data. You should immediately see from the above data that we should have a main table of data with a lookup table for the country data (the data that is currently repeating).
2. Table Analyzer
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.
Because our database table contains repeating information in one or more fields, the Table Analyzer will recommend we split the table into multiple, related tables in which each type of information is stored only once. This process is called normalization. This makes the database more efficient and easier to update as well as 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.
Follow the steps using the slideshow below to confirm your choice:
Screen 1 of the Table Analyzer Wizard. 
Screen 2 of the Table Analyzer Wizard. 
Screen 3 of the Table Analyzer Wizard; select the table. 
Step 4 of the Table Analyzer Wizard; let the Wizard decide. 
Renaming the new tables in Table Analyzer Wizard. 
Confirm the details of the new tables. 
Confirming and selecting Primary Keys of new tables in Table Analyzer Wizard. 
Creating the query for the Table Analyzer Wizard. 
Viewing the One-to-Many relationship created by the Table Analyser tool.
Name the 2 new tables tblData and tblCountries. Note that the original table still exists (renamed to tblImportedData_OLD).
3. Query
See the query that is created as a bonus — it is not the main purpose of this process at all.
You can use the query to update data from more than one table at the same time. The query also offers other time-saving 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.
- The query inherits Lookup fields, from the new tables, 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 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.
References:
- Microsoft (no date) Microsoft Support. Available at: https://support.microsoft.com/en-us/office/normalize-your-data-using-the-table-analyzer-8edbb763-5bab-4fbc-b62d-c17b1a40bbe2 (Accessed: 17 November 2023).