Running Total (Cumulative)

[jamesborne]

Dear programming buddies,


I have a query with the following information:

Field 1 : Date
Field 2: Product
Field 3: Transaction (Purchases/Returns)
Field 4: Quantity

I need a 4th field that contains the running total but it has to be dependent on the "Transaction" meaning if the transaction is "purchase" then decrease the running total and if the transaction is "Returns", then increase the running total.

Also, although its obvious, but the fact that field 2 is "products" means that each product needs its own running total.

Thank you all in advance,
James Borne

[alansidman]

I would handle it in the following manner.
I would have a field for purchases and a field for returns--both quantities. I would not have a field that identifies them as purchases or returns. I would then have an aggregate query that sums the purchases and returns and groups on the product.

I would then create a second query that has the first query as the record source and add a calculation field that subtracts the purchases from the returns to give you a net quantity by product. You will only need to then run the second query as it is dependent on the first one. This is a quick way to achieve what you are looking for.

[jamesborne]

Dear AlanSidman,
Thank you for your quick reply. However, I don't think that's what I'm looking for. I need the running total after each transaction. I think it has something to do with DSum..

But thank you alot for trying to help!

James Borne

[alansidman]

In the footer of your form, you can use the DSum function. You will have to have two fields in your table as I described earlier and subtract one Dsum from the other DSum. If you only want to have one one field, then you will need to enter them as positive and negative numbers.

Look here for the syntax on DSum

http://www.techonthenet.com/access/f...omain/dsum.php

Being very specific about what you are attempting will help to provide you with proper responses. I left my crystal ball at the office so I was guessing at your needs.