Others

Microsoft Access Query to Find New and Changed Records

Phi Hung VoPhi Hung Vo Nov 13, 2026 868 views

Question details

The user needs an Access query that compares a Master table and a Previous table by ID and returns records that are new or have changed Type, Price, or Total values without false matches based on unrelated rows.

Microsoft Access Query to Find New and Changed Records
Product
Microsoft Access
Device & OS
not provided
Scenario
Comparing two Access tables to identify new or changed records in a Master table relative to a Previous table.
Observed behavior
The query must match by unique ID and flag differences in Type, Price, or Total, or records missing from the Previous table.
Before you start

Confirm that the Master and Previous tables both contain a unique ID field with the same data type, and decide whether missing IDs in Previous should be treated as new records.

Solution 1Recommended

Use a RIGHT JOIN SQL query to compare Master and Previous tables

Use this when you want a single Access query that returns new Master records and changes to Type, Price, or Total by matching only the unique ID.

In Access, joining by the unique ID prevents rows from matching only because they share a Type, Price, or Total value. The RIGHT JOIN keeps every record from Master and only matching records from Previous, so missing Previous rows appear with NULL values.

1
Open the query in SQL View

In Microsoft Access, click the Create tab, select Query Design, close the Show Table dialog without adding tables, and switch to SQL View from the View drop-down.

2
Paste the comparison SQL

Enter SELECT M.ID, M.Type, M.Price, M.Total FROM Previous AS P RIGHT JOIN Master AS M ON P.ID = M.ID WHERE M.Type<>P.Type OR M.Price<>P.Price OR M.Total<>P.Total OR P.ID Is Null;

3
Run and verify the results

Click Run on the Query Design ribbon. Confirm the results show changed Master rows and IDs that do not exist in Previous, not rows matched by unrelated field values.

Use a RIGHT JOIN SQL query to compare Master and Previous tables
Query returns new and changed rows: If an ID exists in both tables and all three comparison fields are equal, the row is excluded. If Previous has no matching ID, P.ID is Null and the Master row is included.
Free Microsoft Office alternative

Use WPS Office as a Free, Lightweight Microsoft Office Alternative for Everyday Data Work

WPS Office cannot replace Microsoft Access queries directly, but it offers a free, lightweight Office suite for Word, Excel, and PowerPoint files. Its familiar ribbon UI and strong format compatibility make it easy to open, edit, and share Office documents without a subscription.

Spreadsheet tools with lookup formulas for comparing tabular dataOpens and saves Microsoft Excel, Word, and PowerPoint formats without conversionFree to use with familiar Microsoft Office-style interfaceLightweight performance across Windows, macOS, Android, and iOS
microsoft office alternative - wps office

Frequently Asked Questions

Why does the Access query return duplicate rows for the same ID?

Duplicates usually mean the Previous table contains multiple rows with the same ID. Make the ID unique or create a grouped query that compares one Master row against the latest Previous row.

How can I include records that exist only in the Master table?

Use a RIGHT JOIN from Previous to Master and include P.ID Is Null in the WHERE clause. This keeps unmatched Master rows where Previous has no matching ID.

Can I compare more than Type, Price, and Total?

Yes. Add an OR condition for each additional field in the WHERE clause, such as OR M.Quantity<>P.Quantity, and include the field in the SELECT list.

Does WPS Office have an Access query tool?

WPS Office does not include an Access database application. It provides Writer, Spreadsheets, Presentation, and PDF tools, and can open or save Word, Excel, and PowerPoint formats.