How to Append Excel Data to Access Tables with Lookup Fields
Question details
The user needs a safe way to append Excel data into Microsoft Access tables that contain lookup fields when the Excel data holds display values rather than primary key values.
- Product
- Microsoft Access, Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is importing or appending Excel records into related Access tables and needs to preserve database relationships and lookup fields.
- Observed behavior
- Direct append attempts fail or create incorrect records because Excel contains user-friendly display values instead of the related tables' primary keys.
Before importing, identify which Access columns are lookup fields that store foreign keys, and check whether the related Excel columns contain display text or primary key values. Also confirm parent-table records can be created before child-table records.
Use a Staging Table and Append Queries to Convert Display Values into Access Primary Keys
Best for most imports because it isolates Excel data before writing into related Access tables and avoids lookup-field errors.
Access lookup columns usually store numeric foreign keys that reference AutoNumber primary keys in related tables. A combo box can hide that key and show a non-key display value, so Excel often contains the display value rather than the key.
Import Excel into a temporary staging table first, then run append queries that join the staging data to the master tables on matching non-key values. Insert parent records before child records so every foreign key already exists.
In Access, go to External Data > New Data Source > From File > Excel, select the Excel file, and import the worksheet into a temporary staging table.
Click Create > Query Design, add the staging table and the parent master table such as Invoices, join them on a matching non-key value, and change the query type to Append to the parent table.
Create another append query that joins the staging table to the related master table such as Orders or Units on the display value column, and append the related primary key into the foreign-key column of the child table.
Run the parent-table append query first, verify the new primary keys, then run child-table append queries to populate the foreign-key columns correctly.

Link the Excel File and Decompose It with Separate Parent and Child Append Queries
Useful when you prefer to keep Excel linked instead of importing a staging copy, and when Excel data is not organized relationally.
Manage Excel Data in WPS Office Before Importing to Access
Because this error happens in Microsoft Access and concerns Access lookup fields, it cannot be fixed inside WPS Office. WPS Office is a free, lightweight Microsoft Office alternative that edits Excel-compatible .xlsx files with a familiar ribbon UI, making it easy to clean and prepare data before you import it into Access.

Frequently Asked Questions
Why can't I append Excel data directly into Access lookup columns?
Because Access lookup columns store the related table's primary key, usually an AutoNumber. If Excel contains display text instead of that numeric key, Access cannot match it, causing an import error.
What is the safest way to import Excel records into related Access tables?
Import the Excel data into a temporary staging table, then run append queries that join the staging table to the referenced tables on matching non-key values. Append parent records before child records.
In what order should I run append queries?
Run parent-table append queries first—for example create the Invoice record before Invoice Details—so the primary key exists for the child table's foreign-key column.
Can WPS Office import Excel data into Microsoft Access?
WPS Office does not include Microsoft Access, so it cannot open .accdb databases. Use WPS Spreadsheets to edit and clean .xlsx data, then import it into Access using the staging table approach.
How do I link an Excel file in Microsoft Access?
In Access, click External Data > New Data Source > From File > Excel, choose "Link to the data source by creating a linked table", select the worksheet, and finish the wizard.




