SQL help needed please

C

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
 
T

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
 
J

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
 
C

cinnie

That does it! Thanks.
--
cinnie


John Spencer said:
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

.
 
C

cinnie

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

John Spencer

Of course. My error.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top