Compare 2 Tables help needed (1 Viewer)

Hi everyone,

I have two tables (ClaimDoc) and (ClaimDocCopy) in a database (CIS) that are not linked.
ClaimDocCopy is used by other people and they need to change data in any field when necessary. I import the ClaimDocCopy in every week after deleting the previous copy.

ClaimDoc is for my use only and I also need to make changes to any field as needed.
Both tables have the same records (ClientAcctNo, AuditCode and ClaimNo) but could have different ID's.
Both tables can and do have occasional null values except in ID, ClientAcctNo, AuditCode and ClaimNo.

These are the fields in both tables:
ID
AuditCode
ClientAcctNo
ClaimNo
DocumentNo
DocDate
DocAmt
Invoiced
HospCode
RecordedDate
PaymentNo
PaymentDate
GL Acct
Assumed

I want to be able to compare ClaimDoc and ClaimDocCopy (all fields) to see what is different between the two tables (in all fields) and decide if different, which is more relevant.

I’ve had no luck with Find Unmatched Query and would like something in either VBA or SQL that I can alter later if my needs change (field names or other fields added later).

I see several possibilities on here, but I don’t seem to be able to alter them to fit my needs and have them work. Help will be appreciated and thank you in advance.

If the purpose is to identify which fields have been changed, you can Inner Join the tables on those 3 fields. Add a criteria that concat all fields on both, eg:


Where table1.field1 & table1.field2 & ... <> table2.field1 & table2.field2 & ...

Thank you for the reply arnelgp. I tried the inner join and when a field was changed from null to anything, it was omitted from the result. I'm not sure what you mean when you say to 'add a criteria that concatenate all fields on both'. Can you give a short example of what you mean? And will it help with the null fields?