SELECT DISTINCT query from three sources possible?

[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

 

[Allen Browne]

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.)

 

[tedmi]

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

 

[Clif McIrvin]

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

(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.

 

[Arvin Meyer [MVP]]

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.

 

[EagleOne]

I appreciate all of the information; each with interesting subilties

EagleOne

 

[tedmi]

Oh right, I forgot that UNION is DISTINCT by default. My second query is
unnecessary.

 

[EagleOne]

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;

 

[EagleOne]

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;

 

[Douglas J. Steele]

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;

 

[EagleOne]

Doug,

Bravo! You answered my next question.

 

[Douglas J. Steele]

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.

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;