Making an Append Query from an unmatched Query

[DZ]

Hello

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. I
also want to NOT return any records where Name or Address are null.

The unmatched below query is returning the correct records. Is there a way
to Append these records to Table2 and do it all in a single SQL statement?

Thanks for any help with this.

Below is the unmatched query.

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

 

[John Spencer]

If all you want to do is append that result to Table 2, then all you need is
the following.

INSERT INTO Table2 ([Name],[Address],[Location])
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

If you want additional fields and they will return more duplicate records
based on the three field requirement then if it makes no difference which
values are used from the additional field(s), use a Totals (aggregate) query
as the source. That query might look like the following

INSERT INTO Table2 ([Name],[Address],[Location])
SELECT Table1.[Name]
, Table1.[Address]
, Table1.Location
, First(Table1.HomePhone)
, First(Table1.WorkPhone)
, First(Table1.EmailAddress)
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
GROUP BY Table1.[Name]
, Table1.[Address]
, Table1.Location

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