UnMatched Query

[DZ]

Hi

I want to create an unmatched query that returns unique records.

Explanation:
I want to return records containing the Name, Address and Location fields
only, from Table1 where the combined fields Name, Address and Location are
are not in Table2...and I want each record that is returned to be unique.

Here is the SQL. I tried DISTINCROW also, but the 3 field records returned
are not unique.

SELECT DISTINCT Table1.[Name], Table1.[Address], Table1.Location
FROM Table1 LEFT JOIN Table2 ON (Table1.[Address] = Table2.[Address]) AND
(Table1.[Name] = Table2.[Name]) AND (Table1.Location = Table2.Location)
WHERE (((Table2.Location) Is Null) AND ((Table2.[Name]) Is Null) AND
((Table2.[Address]) Is Null));


Ultimately what I want to do is to add the unique records from the above
Query to Table 2 (Append Query).


Thanks for any help with this.

 

[DZ]

Thanks for responding

I am getting the same results with ORs. and records are not unique

 

[John Spencer]

If that is returning records that are not unique values then I would
guess that you are not seeing why the records are not unique. The query
should work.

SELECT DISTINCT Table1.[Name]
, Table1.[Address]
, Table1.Location
FROM Table1 LEFT JOIN Table2
ON (Table1.[Address] = Table2.[Address]) AND
(Table1.[Name] = Table2.[Name]) AND
(Table1.Location = Table2.Location)
WHERE Table2.Location Is Null
Order By Table1.[Name]
, Table1.[Address]
, Table1.Location


By the way you only need to test one of the three fields for null.
Since for the join to work all three fields must have a value (Nulls
never match) or there will not be a matching record found in table2.

I would look for extra spaces within the fields

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================