SELECT DISTINCT query from three sources possible?

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003

Have Tables A, B, C all which contain source data.

Each table has a field AccountNumber.

I am attempting to create a table that has a list of all AccountNumbers used in A and/or B and/or C.
This table should have only one instance of an account number no matter which table.

The follouing code works but produced all combo's and permutations of three sets of AccountNumbers
SELECT DISTINCT A.accountnumber, B.accountnumber, C.accountnumber
INTO AccountNumbers
FROM A, B, C;

What SQL syntax would give me one-instance of each account number in the three tables?

TIA EagleOne
 
Try a UNION query:
SELECT accountnumber FROM A
UNION SELECT accountnumber FROM B
UNION SELECT accountnumber FROM C
ORDER BY accountnumber;

Using UNION will automatically de-duplicate.
(Use UNION ALL when you don't want it deduplicated.)

Ultimately, this is a fudge. If A, B, and C are permanent tables in your
database, you might want to consider whether they would be better as one
table (with an extra field to tell whatever the difference is.)
 
Create a union query:
SELECT accountNumber FROM A
UNION
SELECT AccountNumber FROM B
UNION
SELECT AccountNumber FROM C

Then run another query:
SELECT DISTINCT AccountNumber FROM YourUnionQuery
 
2003

Have Tables A, B, C all which contain source data.

Each table has a field AccountNumber.

I am attempting to create a table that has a list of all
AccountNumbers used in A and/or B and/or C.
This table should have only one instance of an account number no
matter which table.

The follouing code works but produced all combo's and permutations of
three sets of AccountNumbers
SELECT DISTINCT A.accountnumber, B.accountnumber, C.accountnumber
INTO AccountNumbers
FROM A, B, C;

What SQL syntax would give me one-instance of each account number in
the three tables?

TIA EagleOne
Click to expand...


(Do you need to create a new table? Why not just use a query to produce
the list of unique account numbers?)

Untested:
Maybe

SELECT DISTINCT accountnumber
FROM SELECT DISTINCT A.accountnumber, B.accountnumber, C.accountnumber
ORDER BY accountnumber

The documentation says FROM requires a table or saved query, so I don't
know if this will actually work.

I use nested queries regularly ... imo it's easier to debug and maintain
multiple queries that are fairly simple, and I have the impression that
there is no significant performance hit from nesting queries.
 
A Union query would work:

SELECT DISTINCT accountnumber FROM A
UNION
SELECT DISTINCT accountnumber FROM B
UNION
SELECT DISTINCT accountnumber FROM C;

Then name that query and run a second query on that to add your data to the
table.
 
To all:

Tables A,B,C are in XYZ.mdb

I am getting the error message Cannot find c:\myFolder\A.mdb

Apparently, I need to change syntax toI "call" each table.

To no avail I tried i.e.:

SELECT accountnumber FROM [A].accountnumber
UNION SELECT accountnumber FROM .accountnumber
UNION SELECT accountnumber FROM [C].accountnumber
ORDER BY accountnumber;

-and-

SELECT accountnumber FROM (A) accountnumber
UNION SELECT accountnumber FROM (B) accountnumber
UNION SELECT accountnumber FROM (C) accountnumber
ORDER BY accountnumber;
 
Never mind! Just getting over another brainfart.

This works fine:
SELECT accountnumber FROM [A]
UNION SELECT accountnumber FROM
UNION SELECT accountnumber FROM [C]
ORDER BY accountnumber;
 
Assuming that the query isn't running in XYZ.mdb and that you don't have
linked tables pointing to tables A, B and C in XYZ.mdb:

SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].[A].accountnumber
UNION
SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb]..accountnumber
UNION
SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].[C].accountnumber
ORDER BY accountnumber;
 
Actually, I didn't. Don't know what I was thinking of! That should be:

SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].[A]
UNION
SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].
UNION
SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].[C]
ORDER BY accountnumber;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

Bravo! You answered my next question.

Douglas J. Steele said:
Assuming that the query isn't running in XYZ.mdb and that you don't have
linked tables pointing to tables A, B and C in XYZ.mdb:

SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].[A].accountnumber
UNION
SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb]..accountnumber
UNION
SELECT accountnumber
FROM [;DATABASE=C:\myFolder\XYZ.mdb].[C].accountnumber
ORDER BY accountnumber;
Click to expand...
Click to expand...
 
Back
Top