I want to summarise fields in Access

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

Guest

I need to summarise my data a bit better. I have a query that looks like
this:-
DESCRIPTION QTY COST CENTRE SEPT OCT
ACCU-CHECK ACTIVE* -2 C £16.40 -£33.07
ACCU-CHECK ACTIVE* -2 C £32.81 -£33.07
ACCU-CHECK ACTIVE* -2 D £49.21 -£33.07
ACCU-CHECK ACTIVE* 1 D £16.40 £16.54

I would like to add all the Descriptions that are the same to give me a
total each month for each cost.centre. But I can't seem to figure out how to
do this. I should have a total for Accu-Check for Cost.Centre C for each
month and a total for Accu-Check for Cost. Centre D for each month. Can
anyone help?
 
Hi,
All you have to do is to build another query based on your query.

SELECT Query.DESCRIPTION, Query.[COST CENTRE], Sum(Query.QTY) AS SumOfQTY,
Sum(Query.SEPT) AS SumOfSEPT, Sum(Query.OCT) AS SumOfOCT
FROM Query
GROUP BY Query.DESCRIPTION, Query.[COST CENTRE];

I would modify your original query, but I do not want to confuse you. Hope
this will give you some idea to help you to modify your original query.
 
Hi,


Since a record can belong to just one group, you have to duplicate the
record


If you have

Data ' table name
Company, State, Period, Amount ' fields name


and got

Cie State Sept Oct
IBM US 1010 1111
IBM UK 1212 1313
MSFT US 1414 1515




and want to get


Cie State Sept Oct
IBM US 1010 1111
IBM UK 1212 1313
MSFT US 1414 1515
IBM -ALL- 2222 4444
MSFT -ALL- 1414 1515






change:

....
FROM myData As a
GROUP BY a.cie, a.state
....

to

....
FROM myData As a, myBool As b
GROUP BY a.cie, iif(b.bool, a.sate, "-ALL-")
....



where table myBool is a table with one field, bool, two records, one with a
value True, one with a value False.


(Technically, that also assumes there is no "state" with the name
"-all-" ).



In MS SQL Server, you can do it more simply just using a ROLLUP. Here, with
Jet, the cross join duplicate (by 2) each records from myData, association
one of the two duplicated row with True, the other one with False. Each
initial row can then be in TWO different groups, and we rebuilt the GROUP
clause accordingly.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top