Subtotals / sums

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data that looks like this in a query from multiple tables. and would like to return sums of categories.

Cust number Customer Name ProdCode SalesMo1 Salesmo2 SalesMoX
123 ABC Corp 100 100 100 100
123 ABC Corp 110 0 50 50
123 ABC Corp 120 75 100 100
123 ABC Corp 200 0 0 35
123 ABC Corp 220 50 50 50
123 ABC Corp 350 0 50 50
123 ABC Corp 400 100 100 100
123 ABC Corp etc
124 B Corp 100 0 0 50
124 B Corp 130 100 100 100
etc

I would like the result to Sum Product Codes 100 to 199, 200 to 299, and 300 to 399 etc. and return something like,

123 ABC Corp 175 250 250
123 ABC Corp 50 50 85
123 ABC Corp 0 50 50
etc
124 B Corp 100 100 150

any suggestions? If I try to use multiple queries focusing on each parameter, I eliminate customers that do not have any data in a particular query (a customer that has not purchased anything from product codes 100 to 199 will not be shown in the query, and when combined, I lose that customer)
 
Dear M Rodgers:

It might be easier to write the query based on the underlying tables,
but based on just the query you reported:

SELECT [Cust number], [Customer Name],
(SELECT SUM(SalesMo1) FROM YourQuery Q1
WHERE Q1.[Cust number] = Q.[Cust number]
AND Q1.ProdCode BETWEEN 100 AND 199) AS [100 to 199],
(SELECT SUM(SalesMo1) FROM YourQuery Q1
WHERE Q1.[Cust number] = Q.[Cust number]
AND Q1.ProdCode BETWEEN 200 AND 299) AS [200 to 299],
(SELECT SUM(SalesMo1) FROM YourQuery Q1
WHERE Q1.[Cust number] = Q.[Cust number]
AND Q1.ProdCode BETWEEN 300 AND 399) AS [300 to 399]
FROM YourQuery Q

The above will sum only on SalesMo1. I'm guessing you may also want
to sum Salesmo2 and perhaps other values. If so, a crosstab won't do
it.

Watch out for the ProdCode values filtered in the BETWEEN clauses
above. I didn't know if they might be text values, and if so, whether
they would include leading spaces. So, these may need adjustment.

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
M.

Where you currently have the ProdCode Field in your query, insert a
new column Categories: Int(ProdCode/100)*100.

Then make the query an aggregate query and group by Cust#, CustName,
Category, and summ the SalesMo1, SalesMo2, SalesMoX columns.

--
HTH

Dale Fye


I have data that looks like this in a query from multiple tables. and
would like to return sums of categories.

Cust number Customer Name ProdCode SalesMo1 Salesmo2
SalesMoX
123 ABC Corp 100 100
100 100
123 ABC Corp 110 0
50 50
123 ABC Corp 120 75
100 100
123 ABC Corp 200 0
0 35
123 ABC Corp 220 50
50 50
123 ABC Corp 350 0
50 50
123 ABC Corp 400 100
100 100
123 ABC Corp etc
124 B Corp 100 0
0 50
124 B Corp 130 100
100 100
etc

I would like the result to Sum Product Codes 100 to 199, 200 to 299,
and 300 to 399 etc. and return something like,

123 ABC Corp 175
250 250
123 ABC Corp 50
50 85
123 ABC Corp 0
50 50
etc
124 B Corp
100 100 150

any suggestions? If I try to use multiple queries focusing on each
parameter, I eliminate customers that do not have any data in a
particular query (a customer that has not purchased anything from
product codes 100 to 199 will not be shown in the query, and when
combined, I lose that customer)
 
An aggregate query grouped on int(ProdCode/100)*100 should suffice.
Peter

mrodgers3 said:
I have data that looks like this in a query from multiple tables. and
Click to expand...
would like to return sums of categories.
Cust number Customer Name ProdCode SalesMo1 Salesmo2 SalesMoX
123 ABC Corp 100 100 100 100
123 ABC Corp 110 0 50 50
123 ABC Corp 120 75 100 100
123 ABC Corp 200 0 0 35
123 ABC Corp 220 50 50 50
123 ABC Corp 350 0 50 50
123 ABC Corp 400 100 100 100
123 ABC Corp etc
124 B Corp 100 0 0 50
124 B Corp 130 100 100 100
etc

I would like the result to Sum Product Codes 100 to 199, 200 to 299, and
Click to expand...
300 to 399 etc. and return something like,
123 ABC Corp 175 250 250
123 ABC Corp 50 50 85
123 ABC Corp 0 50 50
etc
124 B Corp 100 100 150

any suggestions? If I try to use multiple queries focusing on each
Click to expand...
parameter, I eliminate customers that do not have any data in a particular
query (a customer that has not purchased anything from product codes 100 to
199 will not be shown in the query, and when combined, I lose that customer)
 
Back
Top