Find records with matching field value & compare records showing non-matching

[whitelockben]

Hi all,



I have two table with matching structures and I want to compare records between the two tables where field 1 is identical. However on one table some of the records appear more that once.

eg:

TBL1
Field 1 Field 2 Field 3
A _____x_____ x
C _____x_____ x
D _____x_____ y

TBL2
Field 1 Field 2 Field 3
A_____ x_____ x
C _____x_____ x
D _____x_____ y

I want records that dont match to then be shown.

Ie
Field 1 Field 2 Field 3 Field 4 (difference)
D _______x______y______ x

Is this possible?

[Stingaway]

Basics: put the two fields together in a query with a left join on the field 1, adding each field that you want to compare from both tables. In the query, put a condition on the second table field column you are trying to compare - 'not like table1.fieldname' or similar.

If by difference (field 4) you mean a number, just put another column in your query that is =[field2]- [field3] and call it field4...

Hope that helps. It's tricky to try to visualize what you are seeing...