I’m trying to write an unmatched select query and can’t seem to get it to work.
I want to select data from Query1 that doesn’t already exist in Table1. There can be duplicate values in any field, but it should only be selected if the combination of values in all fields doesn’t exist in Table1.
I created Example 1 with the Unmatched Query Wizard. It works, but only compares 1 field.
I tried to link all the fields in Example 2 and 3, but both return all records from Query 1.
Example 1:
SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4, [Query1].Field5
FROM [Query1] LEFT JOIN Table1 ON [Query1].Field1 = Table1.Field1
WHERE (((Table1.Field1) Is Null));
Example 2:
SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4, [Query1].Field5
FROM [Query1] LEFT JOIN Table1 ON ([Query1].Field5 = Table1.Field5) AND ([Query1].Field4 = Table1.Field4) AND ([Query1].Field3 = Table1.Field3) AND ([Query1].Field2 = Table1.Field2) AND ([Query1].Field1 = Table1.Field1)
WHERE (((Table1.Field1) Is Null) AND ((Table1.Field2) Is Null) AND ((Table1.Field3) Is Null) AND ((Table1.Field4) Is Null) AND ((Table1.Field5) Is Null));
Example 3:
SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4, [Query1].Field5
FROM [Query1] LEFT JOIN Table1 ON ([Query1].Field5 = Table1.Field5) AND ([Query1].Field4 = Table1.Field4) AND ([Query1].Field3 = Table1.Field3) AND ([Query1].Field2 = Table1.Field2) AND ([Query1].Field1 = Table1.Field1)
WHERE (((Table1.Field1) Is Null)) OR (((Table1.Field2) Is Null)) OR (((Table1.Field3) Is Null)) OR (((Table1.Field4) Is Null)) OR (((Table1.Field5) Is Null));
I ultimately want to append this data to Table1.
I want to select data from Query1 that doesn’t already exist in Table1. There can be duplicate values in any field, but it should only be selected if the combination of values in all fields doesn’t exist in Table1.
I created Example 1 with the Unmatched Query Wizard. It works, but only compares 1 field.
I tried to link all the fields in Example 2 and 3, but both return all records from Query 1.
Example 1:
SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4, [Query1].Field5
FROM [Query1] LEFT JOIN Table1 ON [Query1].Field1 = Table1.Field1
WHERE (((Table1.Field1) Is Null));
Example 2:
SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4, [Query1].Field5
FROM [Query1] LEFT JOIN Table1 ON ([Query1].Field5 = Table1.Field5) AND ([Query1].Field4 = Table1.Field4) AND ([Query1].Field3 = Table1.Field3) AND ([Query1].Field2 = Table1.Field2) AND ([Query1].Field1 = Table1.Field1)
WHERE (((Table1.Field1) Is Null) AND ((Table1.Field2) Is Null) AND ((Table1.Field3) Is Null) AND ((Table1.Field4) Is Null) AND ((Table1.Field5) Is Null));
Example 3:
SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4, [Query1].Field5
FROM [Query1] LEFT JOIN Table1 ON ([Query1].Field5 = Table1.Field5) AND ([Query1].Field4 = Table1.Field4) AND ([Query1].Field3 = Table1.Field3) AND ([Query1].Field2 = Table1.Field2) AND ([Query1].Field1 = Table1.Field1)
WHERE (((Table1.Field1) Is Null)) OR (((Table1.Field2) Is Null)) OR (((Table1.Field3) Is Null)) OR (((Table1.Field4) Is Null)) OR (((Table1.Field5) Is Null));
I ultimately want to append this data to Table1.
