Sum of Qty

[Gonzalo]

I created a query with two fields, first field is the qty field, 2nd field is
the description to the qty field (Router), I'm summing up the qty of each
description (Router), The query gives me totals by description (router), the
descriptions for the qty's are 4 different discriptions (router) this is what
my query looks like. what I need is for the qty for the description(Router)
"Loose Cases' always take the qty for "Sort & Segregate" and give me a sum of
both description under the Loose Cases.

Example fo my query
SumOfQty Router (Descriptions)
73186 Loose Cases
16743 Palletized Cases
39411 Sort & Segregate
300 Off Standard


Example of what I want my query to be.
This is what I want my answer to be. I added the qty for Loose Cases + Sort
and Segregate.

SumOfQty Router (Description)
112597 Loose Cases
16743 Palletized Cases
39411 Sort & Segregate
300 Off Standard


thank you in advance Gonzalo

 

[Ron2006]

I created a query with two fields, first field is the qty field, 2nd fieldis
the description to the qty field (Router), I'm summing up the qty of each
description (Router), The query gives me totals by description (router), the
descriptions for the qty's are 4 different discriptions (router) this is what
my query looks like. what I need is for the qty for the description(Router)
"Loose Cases' always take the qty for "Sort & Segregate" and give me a sumof
both description under the Loose Cases.

Example fo my query
SumOfQty       Router (Descriptions)
73186             Loose Cases
16743             Palletized Cases
39411             Sort & Segregate
300                 Off Standard

Example of what I want my query to be.
This is what I want my answer to be. I added the qty for Loose Cases + Sort
and Segregate.

SumOfQty       Router (Description)
112597           Loose Cases
16743             Palletized Cases
39411             Sort & Segregate
300                 Off Standard

thank you in advance Gonzalo

You are asking the query to, in effect, count one record in two
places.
One way to do this is to fake it out by duplicating the one set of
records.

A) Create a query that sums records and that has a criteria of "Sort &
Segregate" BUT for the value of the output field "Router(Description)"
has "Loose Cases"

B) Create a second query that sums records the normal way. Make sure
that the field names for A above and this query are identical.

C) Create a union query of the previous two Queries

D) Create a sum query that does the sum/group of the Union Query in
item C above.

OR


A) Create a query that sums records and that has a criteria of "Sort &
Segregate" BUT for the value of the output field "Router(Description)"
has "Loose Cases". Have the output field names be identical to the
table being summed. It can alternately simply be a select query.with
the appropriate value change.

B) Create a union query of the original table AND the query created
above.

C) Create a sum query that does the sum/group of the Union Query in
item B above.


Ron

 

[Ken Sheridan]

Try this:

SELECT Router, SUM(Qty) As SumOfQty
FROM YourTable
WHERE Router <> "Loose Cases"
GROUP BY Router
UNION ALL
SELECT "Loose Cases", SUM(Qty)
FROM YourTable
WHERE Router IN("Loose Cases", "Sort & Segregate");

Ken Sheridan
Stafford, England