Summing Multiple Columns from Multiple Queries

[Snowguy]

Here is my dilemma

I work for a vegetable packer, who brings in tote crates or tote bags of
produce and repacks it. As tags are taken off crates I enter them in a table
of product received. When a crate is dumped it receives a date on the date it
was dumped. I created 3 queries from this inventory table.
----------
The queries from the main table are:

One query for delivered crates, which could be a total of all crates recieved
for the year. This is a sum of all crates delivered using the "Sum" function
on the "number of crates" field.

One query for all dumped crates based on "Is not null" value for the "Date
Dumped" field.

One query for the undumped crates, which is our present inventory, based on
the "null value for the "date dumped" field in the main table.

----------

I then have created 3 individual reports giving associated totals of tote
crates or bags and the associated differing sizes of product in these bags or
crates.

The question is------How do I summarize all of the information in the 3
queries into one report with a delivered column, a dumped column, and an
undumped column. All data would have to match vertically, with 0's which are
ommitted in queries showing in the grid and associated sub-totals showing at
the bottom of the report.

I have tried everything and so far I can't seem to find the right combination.
I have read some posts on UNION queries, but I'm not very familiar with
writing SQL queries manually.

---------



Delivered prod | Dumped| Undumped

_______________________________
Vegetable Variety Tote Bags Small size |
5 | 3 | 2

Tote Boxes Medium size
| 4 | 1 | 3

Large
size | 6 | 5 | 1

_______________________________

Total 15 9 6


Any help would be appreciated
Barry

 

[Smartin]

Here is my dilemma

I work for a vegetable packer, who brings in tote crates or tote bags of
produce and repacks it. As tags are taken off crates I enter them in a table
of product received. When a crate is dumped it receives a date on the date it
was dumped. I created 3 queries from this inventory table.
----------
The queries from the main table are:

One query for delivered crates, which could be a total of all crates recieved
for the year. This is a sum of all crates delivered using the "Sum" function
on the "number of crates" field.

One query for all dumped crates based on "Is not null" value for the "Date
Dumped" field.

One query for the undumped crates, which is our present inventory, based on
the "null value for the "date dumped" field in the main table.

----------

I then have created 3 individual reports giving associated totals of tote
crates or bags and the associated differing sizes of product in these bags or
crates.

The question is------How do I summarize all of the information in the 3
queries into one report with a delivered column, a dumped column, and an
undumped column. All data would have to match vertically, with 0's which are
ommitted in queries showing in the grid and associated sub-totals showing at
the bottom of the report.

I have tried everything and so far I can't seem to find the right combination.
I have read some posts on UNION queries, but I'm not very familiar with
writing SQL queries manually.

Hi Barry,

Assuming you have three working queries to start with, you can UNION
them together.

Your first query returns delivered crates, so you probably have
something like

SELECT
SUM(Crates) As Delivered
FROM...

Similarly, your "dumped" query might look like

SELECT
SUM(Crates) As Dumped
FROM...
WHERE
DateDumped Is Not Null

Finally the "undumped",

SELECT
SUM(Crates) As Undumped
FROM...
WHERE
DateDumped Is Null


The way you stitch these together in a UNION query is to make each
category fit into a distinct field. You have three categories, so create
three fields. The fields from each section in a UNION query should
correspond to each other. Where a field is irrelevant in a section, fill
it with Null (or zero; I used the former). The final result will look
something like this:

SELECT
SUM(Crates) As Delivered,
Null As Dumped,
Null As Undumped

UNION

SELECT
Null As Delivered
SUM(Crates) As Dumped,
Null As Undumped
FROM...
WHERE
DateDumped Is Not Null

UNION

SELECT
Null As Delivered,
Null As Dumped,
SUM(Crates) As Undumped
FROM...
WHERE
DateDumped Is Null


HTH