Calculate a running sum in a query

[Guest]

Hi,

This may seem simple but I can't seem to get my head around it........

I have a table with multiple products by multiple store locations and their
yearly sales. I have a query that sorts the data by store location then by
quantity (descending).
I am trying to calculate a running sum (cumulative total) by store. A need
this in a query because I want to use the result to perform an ABC
classification.

Can anybody help???

Thanks,
Mike

 

[Guest]

in query design view of a new query with just your table but no fields yet in
the design:

select the E symbol (greek) off the toolbar which adds the row into the
design grid with 'group by' as the default

then add the Stores Field...leave 'group by' as is

then add the Qty Field - and in this column change the 'groupby' to 'sum'

run that and see if it gives you what you need....then go from there....

 

[Guest]

Thanks for idea but it doesn't give me quite what I was after.

The following data is what I am hoping to see in the as the output from the
query. The Running column is calculating a running total by store. I think it
will need some kind of nested SQL Select statement but I have no idea how to
write it.

Thanks,

Mike

Store Item Qty Running
a 1 12 12
a 2 13 25
b 1 14 14
b 2 12 26
b 3 11 37
c 1 15 15
c 2 13 28

 

[Guest]

got an error when I tried to post new answer - - - here goes again....

ignore first advice - didn't understand your request:

1st Way:

Do running sum in a Report. Using an unbound text box. This is a nice
feature in reports which along with grouping will give you what you want.....

2nd Way:

In a query: put sql statement in as calculated value for your 4th column
and change YourTableName to your table name:

RunAmt: (SELECT Sum(Qty) FROM [YourTableName] As X WHERE X.Item <=
[YourTableName].Item AND X.Store = [YourTableName].Store)

be sure to set up query design as you show left to right and that stores
sort then items sort correctly so the summing is organized correctly too....