Access Query

[Guest]

I have 3 tables that I would like to combine..
Table 1 Table2 Table3
A 10 B 2 C -3
B 20 D 5 E 1
C 30

The query should return
A 10
B 22
C 27
D 5
E 1...How do I go about it?

Thanks

 

[Roger Carlson]

Well, one way is to use two queries. The first is a UNION query to put the
tables together:

qryTable123:
SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3;

Then the second query does the agregating:

SELECT Field1, Sum(Field2) AS SumOfField2
FROM qryTable123
GROUP BY Field1;

However, there is a seriously weird, undocumented format for using a
subquery in the FROM clause - essentially as another table. It won't work if
you have spaces or special characters in the names of tables or fields
(which I don't like anyway). Because of the syntax, *this* sort
of subquery cannot itself contain a subquery of the same format. The syntax
is:

[insert your query here]. As SomeAlias

The left and right square brackets are required, the dot after the right
bracket is required, and the As and alias are required. You can't do
ANYTHING that would require the use of square brackets, inside the square
brackets - that's why no spaces or special characters in
names.

Here's the syntax:
SELECT Field1, Sum(Field2) AS SumOfField2
FROM [SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3]. AS Table123
GROUP BY Field1;

 

[Guest]

Thankyou

-----Original Message-----
Well, one way is to use two queries. The first is a UNION query to put the
tables together:

qryTable123:
SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3;

Then the second query does the agregating:

SELECT Field1, Sum(Field2) AS SumOfField2
FROM qryTable123
GROUP BY Field1;

However, there is a seriously weird, undocumented format for using a
subquery in the FROM clause - essentially as another table. It won't work if
you have spaces or special characters in the names of tables or fields
(which I don't like anyway). Because of the syntax, *this* sort
of subquery cannot itself contain a subquery of the same format. The syntax
is:

[insert your query here]. As SomeAlias

The left and right square brackets are required, the dot after the right
bracket is required, and the As and alias are required. You can't do
ANYTHING that would require the use of square brackets, inside the square
brackets - that's why no spaces or special characters in
names.

Here's the syntax:
SELECT Field1, Sum(Field2) AS SumOfField2
FROM [SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3]. AS Table123
GROUP BY Field1;


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have 3 tables that I would like to combine..
Table 1 Table2 Table3
A 10 B 2 C -3
B 20 D 5 E 1
C 30

The query should return
A 10
B 22
C 27
D 5
E 1...How do I go about it?

Thanks

.

 

[German Saer]

Two Queries:

SELECT * FROM Table1 UNION SELECT * FROM Table2 UNION SELECT * FROM Table3;

..and save it with the name QUnion.

Then

SELECT QUNION.letter, Sum(QUNION.number) AS SumOfnumber FROM QUNION GROUP BY
QUNION.letter;