Others

Normalize Excel Data in Access Using Make-Table Queries

Huma Ashraf ChHuma Ashraf Ch Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

Build predefined relational tables from staged Excel data

Use this approach for a production database that needs primary keys, foreign keys, and referential integrity.

1
Import the spreadsheet

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.

2
Define destination tables

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.

3
Find possible duplicates

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.

4
Review inconsistent records

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.

5
Populate and relate the tables

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.

Review identity matches: Similar names or addresses do not prove that two rows represent the same person. A query can flag candidates, but a person should decide how to merge or correct them.
Free Microsoft Office alternative

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.

Review spreadsheet rows and columns before importing them into AccessOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
QA img-9

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.