SQL help needed please

[cinnie]

greetings gurus

I am having trouble with the syntax of a query I'm trying to write. Here is
the situation, simpified to its essence:
C:/DB/A.mdb has tblNames with relevant fields CustID, CustName, CustEMail.
C:/DB/B.mdb has the same table and fields.
C:/DB/C.mdb also has the same table and fields.

Many of the names appear in 2 or 3 of the databases, but most names appear
in only one of the three databases.

Here's what I am trying to do (so far without success)...

I want the values of CustID, CustName, CustEMail that are in A.mdb
UNION
the values of CustID, CustName, CustEMail that are in B.mdb.
Then I need to remove any doubles (same PK CustID) from the resulting
recordset.
Finally, I need to EXCLUDE any of these remaining records that ALSO appear
in C.mdb.

I would be SO greatful for help. - cinnie

 

[Tom van Stiphout]

On Fri, 12 Feb 2010 15:49:02 -0800, cinnie

You should link the tables from B and C to your A database. Give them
different names e.g. tblNamesB, tblNamesC.
Now the union query is trivial, and it will automatically exclude
duplicate records. However, your requirement is to exclude duplicate
PKs, which is a subtly different question. How about:
select * from tblNames
union
select * from tblNamesB where tblNamesB.CustID not in (select CustID
from tblNames)
I think you can figure out the rest.

-Tom.
Microsoft Access MVP

 

[John Spencer]

If you have links in the A.mdb to the relevant table in B.Mdb and C.mdb then
the following should work to give you a unique list of CustID, CustName, and
CustEmail that are in either A or B and not in C.

SELECT A.CustID, A.CustName, A.CustEmail
FROM TableA LEFT JOIN TableC
ON TableA.CustID = TableC.CustID
WHERE TableC.CustID is Not Null
UNION
SELECT B.CustID, B.CustName, B.CustEmail
FROM TableB LEFT JOIN TableC
ON TableB.CustID = TableC.CustID
WHERE TableC.CustID is Not Null

If you want just one record per CustID and there are possible duplicates
records because CustName or CustEmail differs then you can use the UNION query
as the source for another query where you aggregate the data

SELECT CustID, First(CustName), First(CustEmail)
FROM qUnionQuery
GROUP BY CustID

IF you want the latter, you might change UNION to UNION ALL and see if that
returns the results faster or slower.

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

 

[cinnie]

That does it! Thanks.
--
cinnie

If you have links in the A.mdb to the relevant table in B.Mdb and C.mdb then
the following should work to give you a unique list of CustID, CustName, and
CustEmail that are in either A or B and not in C.

SELECT A.CustID, A.CustName, A.CustEmail
FROM TableA LEFT JOIN TableC
ON TableA.CustID = TableC.CustID
WHERE TableC.CustID is Not Null
UNION
SELECT B.CustID, B.CustName, B.CustEmail
FROM TableB LEFT JOIN TableC
ON TableB.CustID = TableC.CustID
WHERE TableC.CustID is Not Null

If you want just one record per CustID and there are possible duplicates
records because CustName or CustEmail differs then you can use the UNION query
as the source for another query where you aggregate the data

SELECT CustID, First(CustName), First(CustEmail)
FROM qUnionQuery
GROUP BY CustID

IF you want the latter, you might change UNION to UNION ALL and see if that
returns the results faster or slower.

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

.

 

[cinnie]

In both cases, I changed "WHERE TableC.CustID is Not Null"
to "WHERE TableC.CustID is Null".

 

[John Spencer]

Of course. My error.

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