Unmatch Query Question

[shaggy]

Table1 (New Data, Purged Periodically)
Table2 (Archived Unique Records from Table1 not Purged)

I'm trying to write an Unmatched Append query to populate Table2 from Table1 with records that don't already exist in Table2

Query (Doesn't work)

SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE (((Table2.Field1) Is Null));

I want records copied from Table1 to Table2 if the entire record (combination of all fields) is unique. The query won't copy any records if Field1 is not unique.

Here's an example:
Each character represents 1 field

Table1
1 2 4 7
2 3 7 2
1 3 9 1

Table2
1 2 4 7
3 2 7 1 *#
1 3 1 2 *
2 3 7 2
4 1 9 2 *#
2 2 1 2 *


* I want these records copied.
# These are the records the query copies.

Basically I'm trying to find Unmatches on the entire record, not the values of any individual fields.

Thanks

 

[Jon K]

Try this query:-

SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.Field1 = Table1.Field1) AND (Table2.Field2 = Table1.Field2) AND (Table2.Field3 = Table1.Field3) AND (Table2.Field4 = Table1.Field4)
WHERE (((Table1.Field1) Is Null));


It is based on these two tables:-
Table1
1 2 4 7
2 3 7 2
1 3 9 1

Table2
1 2 4 7
3 2 7 1 *#
1 3 1 2 *
2 3 7 2
4 1 9 2 *#
2 2 1 2 *


(It seems Table1 and Table2 were switched in your original query.)
.

 

[shaggy]

Unmatched Query

Thanks Jon, that seems to work in this situation.

I have another question though. What if the value of the linked field matches? It seems that the query is selecting records based on Field1 mismatching and then testing all the other fields for mismatches. Is this right?

What if Field1 matches but not any of the other fields?

Would this work?
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.Field4 = Table1.Field4) AND (Table2.Field3 = Table1.Field3) AND (Table2.Field2 = Table1.Field2) AND (Table2.Field1 = Table1.Field1)
WHERE (((Table1.Field1) Is Null)) OR (((Table1.Field1) Is Not Null));

I found a way to do it. It worked on a test of only a few records, but I'm going to be working with 10s of thousands of records and I'm not sure it will work in that case.

Query1: selects new records
Table1: archived records
Union Query2: Query1 & Table1
Query3: unique records of Union Query2
Make table query4: Union Query2 (re-write Table1)