How to Import Excel Data into Microsoft Access While Preserving Relationships
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.

- 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.
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.
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.
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.
Click Create > Query Design, close the Show Table dialog, switch to SQL View, and run: INSERT INTO tblDepartments (Department) SELECT DISTINCT Department FROM tblExcel;
In a new query, run: INSERT INTO tblTitles (Title) SELECT DISTINCT Title FROM tblExcel;
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;
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.
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.

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.

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.




