Data Import & Export

How to Import Excel Data into Microsoft Access While Preserving Relationships

Anum MushtaqAnum Mushtaq Nov 13, 2026 868 views

Question details

The user needs to import employee records from an Excel worksheet into Microsoft Access without manually replacing department and title names with AutoNumber primary key values.

How to Import Excel Data into Microsoft Access While Preserving Relationships
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
Moving employee data from Excel into an Access database that uses AutoNumber primary keys for department and title lookup tables.
Observed behavior
Manually replacing Excel values with generated IDs is unreliable because AutoNumber values are created by Access and may differ between database copies.
Before you start

Make sure the Excel worksheet uses consistent department and title names, and confirm that the destination Access tables exist with AutoNumber primary keys before creating the link and append queries.

Solution 1Recommended

Use a Linked Excel Table and Append Queries to Populate Related Access Tables

Use this method when department and title tables use AutoNumber primary keys and you need Access to generate the correct foreign keys during import.

Linking the Excel file instead of importing it directly keeps the worksheet available as a temporary staging table for queries.

Append queries preserve AutoNumber IDs because Access generates them as rows are inserted, and joining on department and title names supplies the matching foreign keys.

1
Link the Excel worksheet as a temporary table

In Microsoft Access, go to External Data > New Data Source > From File > Excel, select Link to the data source, choose the employee workbook, and name the linked table tblExcel.

2
Append distinct department names

Click Create > Query Design, close the Show Table dialog, switch to SQL View, and run: INSERT INTO tblDepartments (Department) SELECT DISTINCT Department FROM tblExcel;

3
Append distinct title names

In a new query, run: INSERT INTO tblTitles (Title) SELECT DISTINCT Title FROM tblExcel;

4
Append employee records with lookup foreign keys

Create another SQL View query that joins tblExcel to tblDepartments on Department and to tblTitles on Title, then run: INSERT INTO tblEmployees (EmployeeName, DeptID, TitleID) SELECT tblExcel.EmployeeName, tblDepartments.DeptID, tblTitles.TitleID FROM (tblExcel LEFT JOIN tblDepartments ON tblExcel.Department = tblDepartments.Department) LEFT JOIN tblTitles ON tblExcel.Title = tblTitles.Title;

5
Create table relationships

Click Database Tools > Relationships, add tblDepartments, tblTitles, and tblEmployees, then drag tblDepartments.DeptID to tblEmployees.DeptID and tblTitles.TitleID to tblEmployees.TitleID. Select Enforce Referential Integrity and click Create.

6
Verify and remove the temporary link

Open the linked table and the destination tables to confirm row counts and generated IDs, then right-click tblExcel in the Navigation Pane and choose Delete or unlink the linked Excel table.

Use a Linked Excel Table and Append Queries to Populate Related Access Tables
Relationships preserved: The import stores Access-generated foreign keys instead of hard-coded IDs, so relationships remain valid even when the database is copied or recreated.
Free Microsoft Office alternative

Need a Free Way to Prepare Excel Data Before Importing into Access?

WPS Office is a free, lightweight Microsoft Office alternative that opens, edits, and saves .xlsx workbooks so you can clean department, title, and employee data before moving it into Microsoft Access. It offers a familiar ribbon interface and smooth format compatibility.

Open, clean, and save Excel data in .xlsx format for seamless Access import preparation.Free spreadsheet tools for deduplicating and formatting department, title, and employee lists.Familiar ribbon UI makes switching from Microsoft Office easy.Preserves Microsoft Excel file compatibility without the cost.
microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I manually replace department and title names with AutoNumber IDs in Excel?

AutoNumber values are generated by Access and can differ between database copies. Manually entered IDs may point to the wrong parent record or fail referential integrity checks.

What is the difference between linking and importing an Excel worksheet in Access?

Linking creates a live connection to the Excel file as a linked table that you can query, while importing copies the data into Access. Linking is useful as a temporary staging table before appending rows into normalized tables.

Can I use this append-query method in a new Access database?

Yes. Link the Excel worksheet, append unique department and title names, append employee records by joining on those names, and then create relationships using the generated foreign keys.

How do I create relationships after appending employee records?

Open Database Tools > Relationships, add the department, title, and employee tables, then drag each primary key to its matching foreign key. Select Enforce Referential Integrity and click Create.

Does WPS Office include Microsoft Access?

No, WPS Office includes Writer, Spreadsheets, Presentation, and PDF tools but does not include a Microsoft Access database application. You can use WPS Spreadsheets to prepare .xlsx data and then import it in Microsoft Access.