compare field data in two tables

[slimjen]

All; I inherited a database that has two tables that are structured identical;
one called tblcurrentdata and the other called tblpriordata. The user wants a
report or export query that only shows records that has differences between
prior weeks data and the current weeks data. The tables have 12 fields of data
in a record per quote number. The user wants a query or report to identify the
QuoteNum and any changes between the prior and current records. The table
structures are as follows:

tblCurrentData/tblPriorData:
QuoteNum 
CoName
State
ZipcodeNo
priorityColor
CreateDte
ModifyDte
POC
GrossWges
NetWges
SubsidyAmt
BonusAmt

For ex.:
QuoteNum 12345
field 5 (prioritycolor) changed from red in the prior week to green in the
current week and field 7(POC) changed from Scott in the prior week to Jonson in
the current week . The user wants to know this.
QuoteNum 23451 did not have
any changes therefore does not need to be listed in the
query/report
Simply put; compare one table to the other and see whats different right.

Unmatched query doesn’t work because it does not compare
multiple fields. I tried to structure a union query and use <> in each
field but got too tedious and didn’t give the expected results. Does anyone know
of vba code maybe to loop through the fields solution to get these results? Thanks

[ItsMe]

Unmatched query doesn’t work because it does not compare
multiple fields...

It is possible to create multiple Joins. Just be sure to add the Is Null criteria to the additional fields and get the Left Join vs. Right Join thing correct

[CJ_London]

inner join between current and prior on quotenum and 'or' for the criteria

then in criteria

for tblcurrent.coName column put <>tblprior.coName
on the next line down for tblcurrent.state column put <> tblprior.state
on the next line down for tblcurrent.zipcode column put <> tblprior.zipcode
etc

[knarfreppep]

This might help - https://www.accessforums.net/queries...elp-53663.html