Others

How to Exclude Access Records Found in a Linked Excel Table

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user needs to construct a query in Microsoft Access that filters out and excludes records from an Access table if a matching value is present in the first column of a linked Excel table.

How to Exclude Access Records Found in a Linked Excel Table

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering database records by cross-referencing an external linked spreadsheet to find unmatched entries.
Observed behavior: The query should successfully return only the Access table records that do not share a matching identifier with the linked Excel file.

Before you start

Verify that your Excel spreadsheet is successfully linked to your Access database and that the columns being compared share the exact same data type without any unexpected blank or null values.

Solution 1Recommended

Exclude Records Using a NOT IN Subquery

Using a NOT IN SQL subquery is a straightforward method to filter out records that already exist in your linked Excel table.

This method involves writing a simple SQL statement that selects all records from your primary table where the target field's value does not appear in the results of a subquery targeting your linked Excel table.

Open Query Design

Open your Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.

Switch to SQL View

Close the Show Table dialog box without adding any tables. Right-click the query tab and select 'SQL View'.

Enter the Subquery Statement

Type the following SQL statement, replacing the table and field names with your actual names: SELECT * FROM AccessTable WHERE AccessTable.MatchField NOT IN (SELECT LinkedExcelTable.MatchField FROM LinkedExcelTable);

Run the Query

Click the 'Run' button (the red exclamation mark) on the Query Design ribbon to view your filtered results.

Null Value Precaution: If the linked Excel column contains Null values, the NOT IN subquery may return zero results. Ensure your Excel data is clean or add 'WHERE MatchField IS NOT NULL' to your subquery.

Solution 2

Find Unmatched Records Using a LEFT JOIN

A LEFT JOIN combined with a null check is a highly efficient method for excluding matched records, especially useful for larger datasets or when null values are present.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While Microsoft Access is used for complex database management, WPS Office provides a powerful, free alternative to Microsoft Office for your daily document, spreadsheet, and presentation needs. WPS Spreadsheet is fully compatible with Microsoft Excel (.xlsx) formats, making it easy to manage the tables you link to databases.

1. Download the Suite: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install WPS Office: Run the installer and follow the on-screen instructions to set up the software on your device.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel (.xlsx) files directly with perfect format retention.

Fully compatible with Microsoft Excel (.xls, .xlsx) and CSV formats for managing linked tables seamlessly.Free to download and use, with a lightweight installer that saves system resources.Familiar, tabbed user interface makes transitioning from Microsoft Office effortless.Built-in advanced formulas and data analysis tools to prepare data before linking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my NOT IN subquery return no records when unmatched records clearly exist?

This usually occurs if the target column in your linked Excel table contains Null (blank) values. The NOT IN operator evaluates to 'unknown' when comparing against a Null value, which causes the entire query to fail to return rows. To fix this, use the LEFT JOIN method or modify your subquery to exclude nulls: SELECT * FROM T1 WHERE T1.Field NOT IN (SELECT T2.Field FROM T2 WHERE T2.Field IS NOT NULL).

Can I update or delete the Excel table records directly from the Access query?

No. Linked Excel tables in Microsoft Access are strictly read-only. You can query, extract, and view the data, but you cannot edit, add, or delete Excel records directly from within the Access database interface.

Why is the LEFT JOIN method often considered faster than the NOT IN subquery?

A LEFT JOIN is typically optimized much better by the Access database engine, especially for large datasets. Subqueries using NOT IN often force the engine to execute row-by-row comparisons against the entire dataset, which consumes significantly more memory and processing time.

Office Quick Solutions