Normalize Excel Data in Access Using Make-Table Queries
Question details
The user wants to import a large Excel file into Access, identify duplicate or inconsistent data, and split it into normalized relational tables.
- Product
- Microsoft Access and Microsoft Excel
- Device & OS
- not provided
- Scenario
- Setting up an Access database from a large Excel spreadsheet and deciding whether to use make-table queries.
- Observed behavior
- The goal is to create related tables with reliable keys while reviewing possible duplicates and inconsistencies.
Review the Excel column headers and data types, and identify the entities you expect to store separately, such as people and addresses. Decide which fields can reliably identify records and which similar values will require manual review.
Build predefined relational tables from staged Excel data
Use this approach for a production database that needs primary keys, foreign keys, and referential integrity.
In Access, go to External Data > New Data Source > From File > Excel, select the workbook, and use the import wizard to create a staging table. Check the imported field names and data types.
Use Create > Table Design to create a table for each entity. Set a primary key for each table and add foreign-key fields with data types compatible with the keys they reference.
Use Create > Query Wizard > Find Duplicates Query Wizard, or create a totals query, to inspect repeated names, addresses, or other relevant field combinations in the staging table.
Open the query results and compare similar names or addresses manually. Correct the reference data and decide which records represent the same entity before removing redundant records.
Use Create > Query Design to build append queries for the destination tables. Populate parent records first, then use a validated matching field to join staging rows to their parent keys when appending child records. Open Database Tools > Relationships and enable Enforce Referential Integrity after resolving unmatched records.
Use make-table queries for temporary or simple tables
Use a make-table query to create an intermediate table when exploring or preparing imported data.
Review Excel source data with WPS Spreadsheet
WPS Office is a free, lightweight alternative for reviewing spreadsheet data in a familiar interface before an Access import. Its Office format compatibility helps you move existing files with minimal disruption; it does not replace Access relationships or database queries.

Frequently Asked Questions
Can a make-table query create a normalized Access database?
A make-table query can create a table from query results, but it does not automatically establish the keys and relationships for a normalized database. Define those in Table Design and Database Tools > Relationships.
How can I find possible duplicates after importing Excel data?
In Access, choose Create > Query Wizard > Find Duplicates Query Wizard and select the fields to compare. Review the results manually because matching or similar values do not always identify the same entity.
Why should I import Excel data into a staging table first?
A staging table lets you inspect imported field types, identify inconsistent values, and prepare append queries before adding records to the final related tables.
Do I always need a SELECT DISTINCT query for a reference table?
No. DISTINCT can help identify unique combinations in source data, but it is unnecessary if the reference table already contains the distinct values you need. It also cannot determine whether differently spelled names refer to the same person.




