Sum of 3 Tables

[roystonteo via AccessMonster.com]

Dear All,

Is there an query to sum up 3 tables?

Dear All,

Table A: Part No, AQty
Table B: Part No, BQty
Table C: Part No, CQty

Query A: Total_of_AQty
Query B: Total_of_BQty
Query C: Total_of_CQty

How to i get the sum for Table A, B, C?
I can only manage to total up 2 tables quantity by using Union function

 

[John Spencer]

It sounds like you may not be using a UNION query, but are joining
tables together.

A union query can only be built in SQL view and not in Design View
(query grid).

Given the posted table and field names, you will need two queries to
accomplish your goal. A union query to get all the data together and an
aggregate (or totals) query to sum the values.

The SQL for the UNION query would look like

SELECT [Part No], AQty as QTY
FROM [Table A]
UNION ALL
SELECT [Part No], BQty as QTY
FROM [Table B]
UNION ALL
SELECT [Part No], CQty as QTY
FROM [Table C]

Now using that saved union query as the source for a totals query
SELECT [Part no], Sum(QTY) as TotalQty
FROM [The Saved Union query]
GROUP BY [Part No]

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

 

[roystonteo via AccessMonster.com]

John,

You mean i can union join 3 tables together?
Then use 1 query to get the total, is that what u mean?

It sounds like you may not be using a UNION query, but are joining
tables together.

A union query can only be built in SQL view and not in Design View
(query grid).

Given the posted table and field names, you will need two queries to
accomplish your goal. A union query to get all the data together and an
aggregate (or totals) query to sum the values.

The SQL for the UNION query would look like

SELECT [Part No], AQty as QTY
FROM [Table A]
UNION ALL
SELECT [Part No], BQty as QTY
FROM [Table B]
UNION ALL
SELECT [Part No], CQty as QTY
FROM [Table C]

Now using that saved union query as the source for a totals query
SELECT [Part no], Sum(QTY) as TotalQty
FROM [The Saved Union query]
GROUP BY [Part No]

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

Dear All,

[quoted text clipped - 12 lines]

How to i get the sum for Table A, B, C?
I can only manage to total up 2 tables quantity by using Union function

 

[Dale Fye]

Yes, that is what he is recommending.

If you don't mind my asking, why do you have separate tables for each shop?
What you should have is another field in your table indicating the shop.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

John,

You mean i can union join 3 tables together?
Then use 1 query to get the total, is that what u mean?

It sounds like you may not be using a UNION query, but are joining
tables together.

A union query can only be built in SQL view and not in Design View
(query grid).

Given the posted table and field names, you will need two queries to
accomplish your goal. A union query to get all the data together and an
aggregate (or totals) query to sum the values.

The SQL for the UNION query would look like

SELECT [Part No], AQty as QTY
FROM [Table A]
UNION ALL
SELECT [Part No], BQty as QTY
FROM [Table B]
UNION ALL
SELECT [Part No], CQty as QTY
FROM [Table C]

Now using that saved union query as the source for a totals query
SELECT [Part no], Sum(QTY) as TotalQty
FROM [The Saved Union query]
GROUP BY [Part No]

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

Dear All,

[quoted text clipped - 12 lines]

How to i get the sum for Table A, B, C?
I can only manage to total up 2 tables quantity by using Union function

 

[roystonteo via AccessMonster.com]

Dale,

You are right, i will create 1 table with few different shops in that.
Union join shop, partno, qty.

But base on this query, i will only get the total sum of the qty of the
partno from all the shops.
How do i sum the qty and show invidivdual shop name, partno, qty?

SELECT [Part no], Sum(QTY) as TotalQty
FROM [The Saved Union query]
GROUP BY [Part No]

Yes, that is what he is recommending.

If you don't mind my asking, why do you have separate tables for each shop?
What you should have is another field in your table indicating the shop.

John,

[quoted text clipped - 39 lines]

 

[John Spencer]

Add the Shop name to Union query. If the shop name is the name of the
table then use a text literal in the union query.

SELECT "Shop A" as TheShop,[Part No], AQty as QTY
FROM [Table A]
UNION ALL
SELECT "Shop B", [Part No], BQty as QTY
FROM [Table B]
UNION ALL
SELECT "Shop C", [Part No], CQty as QTY
FROM [Table C]

Then in the totals queries add TheShop and group by it.
SELECT TheShop, [Part no], Sum(QTY) as TotalQty
FROM [The Saved Union query]
GROUP BY TheShop, [Part No]

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

Dale,

You are right, i will create 1 table with few different shops in that.
Union join shop, partno, qty.

But base on this query, i will only get the total sum of the qty of the
partno from all the shops.
How do i sum the qty and show invidivdual shop name, partno, qty?

SELECT [Part no], Sum(QTY) as TotalQty
FROM [The Saved Union query]
GROUP BY [Part No]

Yes, that is what he is recommending.

If you don't mind my asking, why do you have separate tables for each shop?
What you should have is another field in your table indicating the shop.

John,

[quoted text clipped - 39 lines]

How to i get the sum for Table A, B, C?
I can only manage to total up 2 tables quantity by using Union function