Compare 2 tables to find out which rows were deleted, added, or changed

[iruwin50]

I need to create a delta table that shows me the differences between two tables (I need to know which rows have been added, which rows have been deleted, and which rows have changed from the old table to the new table). The old and new table have 5 fields with the exact same information. I do not have a primary key in my access database that links the two tables because many of the rows have duplicates in 2 or more fields.

Mind you, I am very new to access and this example below may not be the best example or the best way for what I want to be represented.

Example:
Table1 ("Old")
Field1, Field2, Field3
XX, VV, H
XX, VV, P
YY, GG, N
ZZ, GG, N

Table2 ("New")
Field1, Field2, Field3
XX, VV, S
XX, VV, P
YY, GG, N
HH, FF, C

What I want the query to show

Field1, Field2, Field3, Changes
S Field3 Updated
GB F Field Updated
Row 3 Removed
Row 4 Added



This is what I have done so far.

SELECT Old.*, New.*,
"Field Updated" As Comment
FROM Old
    inner JOIN New
         ON Old.Field1 = New.Field1 AND Old.Field2= New.field2 and Old.field3=New.field4
union
 
SELECT  Old.*, New.*,
 "No Change" As Comment
FROM Old INNER JOIN New ON (Old.Field1 = New.field1) AND (Old.field2 = New.field2) AND (Old.field3 = New.field3) AND (Old.field4= New.field4) AND (Old.field5 = New.field5);
 
 
Union
 
SELECT  Old.*, New.*, 
"Deleted" as Comment
FROM Old LEFT JOIN New ON (Old.field1 = New.field1) AND (Old.field2 = New.field2) AND (Old.field3 = New.field3) AND (Old.[field4] = New.[Field4])
WHERE (((New.Field1) Is Null) AND ((New.Field2) Is Null) AND ((New.Field3) Is Null) AND ((New.field4) Is Null));
 
UNION 
SELECT  Old.*, New.*, "Added" as Comment
 
FROM New LEFT JOIN Old ON (New.field1 = Old.field1) AND (New.field2 = Old.field2) AND (New.field3 = Old.field3) AND (New.field4 = Old.field4) AND (New.[field5] = Old.[field5])
WHERE (((Old.field1) Is Null) AND ((Old.field2) Is Null) AND ((Old.field3) Is Null) AND ((Old.field4) Is Null) AND ((Old.field5) Is Null));

Please I am in desperate need of any advice and suggestions. Please be as specific as possible with your answers since I am knew to this!! Thanks

[NeoPa]

I see this question as borderline acceptable. It is almost a simple request for the work to be done, but there is some work already done, albeit very confused looking and a long way short of practical. Nevertheless, effort has been shown and work has been done already so I'm inclined to go ahead. My approach will be to attempt to lead towards a solution rather than simply to provide one.

To this end I would ask you first to consider (and then explain to us) what should actually be used to identify records from the two tables as matches. What you suggest (That all five fields contain the same values) is inconsistent with the idea of showing where the same records exist but have been changed (As that would make no sense in the scenario described).

When we have a clear idea of exactly what we're looking for I will ask you to do your best to produce each of the queries that go together to make up the whole solution. That way we will be breaking down the problem into manageable chunks. It will also force you to focus your understanding on each of the situations separately, before we bring them all together. A task that will make better sense to you once you've been through each individually.