In TblA, but not in TblB, C, D, or E...

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I need to find out what data is in Table A, but not in
Tables B, C, D, or E. Read below for a better
description...

Table A
Account Number
Account Type
Email
User ID
Alias

Tables B, C, D, E
Account Number
Customer Name
Address 1
Address 2
City, State
Zip
Phone

The relationships between the tables is the Account
Number field in Table A (for instance Tables B, C, D, and
E have the relationship of Account Number to Table A).

I have already run a query for each group of B, C, D, and
E that say "account number in A equals account number in
B" and then one for C, D, and E. However, there is data
in table A that is not in any of the other tables (66
rows to be exact). How do I run a query to say "data in
Table A not in any other table"?

Any help is greatly appreciated,

Andrew C. Moseley
(e-mail address removed)
 
Try something along the lines of either

SELECT accountNumber FROM TableA
WHERE accountNumber NOT IN
(SELECT DISTINCT accountNumber FROM TableB
UNION
SELECT DISTINCT accountNumber FROM TableC
UNION
SELECT DISTINCT accountNumber FROM TableD
UNION
SELECT DISTINCT accountNumber FROM TableD)

OR
SELECT accountNumber FROM TableA
WHERE accountNumber NOT IN
(SELECT DISTINCT accountNumber FROM TableB)
AND accountNumber NOT IN
(SELECT DISTINCT accountNumber FROM TableC)
AND accountNumber NOT IN
(SELECT DISTINCT accountNumber FROM TableD)
AND accountNumber NOT IN
(SELECT DISTINCT accountNumber FROM TableE)

Hope This Helps
Gerald Stanley MCSD
 
Nope, neither of those ideas worked. What if I took
tables B, C, D, and E and merged them into one table and
then just did an "Unmatched Query"?

ACM
 
This might work except for the fact that you have spaces in your field and
tablenames. Access won't allow you to build the subquery if it needs brackets
inside the subquery (and tables and fields with spaces require brackets).

SELECT A.AccountNumber
FROM TableA As A Left JOIN
[SELECT DISTINCT accountNumber FROM TableB
UNION
SELECT DISTINCT accountNumber FROM TableC
UNION
SELECT DISTINCT accountNumber FROM TableD
UNION
SELECT DISTINCT accountNumber FROM TableD]. as B
On A.AccountNumber = B.AccountNumber
WHERE B.AccountNumber is Null

Try joining Table A to each of the other tables as LEFT outer JOIN on each of
the Account numbers. And then test for Account Number being NULL in each of the
other tables.

SELECT A.[Account Number]
FROM [Table A] as A LEFT JOIN [Table B] as B
ON ((((A.[Account Number] = B.[Account Number])
LEFT JOIN [Table C] as C)
ON A.[Account Number] = C.[Account Number])
LEFT JOIN [Table D]) as D
ON A.[Account Number] = D.[Account Number])
LEFT JOIN [Table E]) as E
ON A.[Account Number] = E.[Account Number]
WHERE B.[Account Number] Is Null AND
C.[Account Number] Is Null AND
D.[Account Number] Is Null AND
E.[Account Number] Is Null

 
That is essentially what the UNION query is doing. Try
running solution 1 but change the WHERE clause to
WHERE accountNumber IN
and see if that returns every row in TableA. If it does
then it would suggest that your original hypothesis was
wrong and if it didn't, then something strange is happening
as each row in TableA must be captured by either the
original or the modified UNION query.

Hope This Helps
Gerald Stanley MCSD
 
John

Thanks.

It is so ingrained in me to NEVER have spaces in column and
table names that I forget sometimes what problems do occur
when they are present.

Gerald Stanley MCSD
-----Original Message-----
This might work except for the fact that you have spaces in your field and
tablenames. Access won't allow you to build the subquery if it needs brackets
inside the subquery (and tables and fields with spaces require brackets).

SELECT A.AccountNumber
FROM TableA As A Left JOIN
[SELECT DISTINCT accountNumber FROM TableB
UNION
SELECT DISTINCT accountNumber FROM TableC
UNION
SELECT DISTINCT accountNumber FROM TableD
UNION
SELECT DISTINCT accountNumber FROM TableD]. as B
On A.AccountNumber = B.AccountNumber
WHERE B.AccountNumber is Null

Try joining Table A to each of the other tables as LEFT outer JOIN on each of
the Account numbers. And then test for Account Number being NULL in each of the
other tables.

SELECT A.[Account Number]
FROM [Table A] as A LEFT JOIN [Table B] as B
ON ((((A.[Account Number] = B.[Account Number])
LEFT JOIN [Table C] as C)
ON A.[Account Number] = C.[Account Number])
LEFT JOIN [Table D]) as D
ON A.[Account Number] = D.[Account Number])
LEFT JOIN [Table E]) as E
ON A.[Account Number] = E.[Account Number]
WHERE B.[Account Number] Is Null AND
C.[Account Number] Is Null AND
D.[Account Number] Is Null AND
E.[Account Number] Is Null

.
Click to expand...
 
Back
Top