Running Sum

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

Hi,

I have a table with the folling data (Where R means Received, and S means
Shipped)

TranType Partid Qty
R W293 3
R W293 2
S W293 3
S W293 2

I need to createa a 4th column with a running sum. something like this


TranType Partid Qty Totals
R W293 3 3
R W293 2 5
S W293 3 2
S W293 2 0


Thank you All

Bre-x
 
I assume you are doing this in a report? If not 1st set up an AutoReport
Tabular to give you the basic info you need. Then you need to group your
data; in design view go to View, Sorting & Grouping. I'd guess you want to
group on [PartID]&[Trantype]

In your report bring in the Quantity field again to the detail section and
set the Running Sum property to Over Group. Hope this helps

Sheila
 
Hi,

I have a table with the folling data (Where R means Received, and S means
Shipped)

TranType Partid Qty
R W293 3
R W293 2
S W293 3
S W293 2

I need to createa a 4th column with a running sum. something like this


TranType Partid Qty Totals
R W293 3 3
R W293 2 5
S W293 3 2
S W293 2 0


Thank you All

Bre-x
Click to expand...

The totals should NOT be stored in any table; they can be computed "on
the fly" in a Query or a Report. However, you will need some
additional field to sort the transactions - perhaps a Date/Time field
defaulting to Now() - so that Access can determine in what order to do
the summing. A Table has no usable order by itself.

If you want to display the Totals on a report, simply put a textbox on
the report with a Control Source

=[Qty] * IIF([TranType] = "R", 1, -1)

and set the Running Sum property of that textbox to Over Group. Set
the Report's GroupBy to group on PartID.

John W. Vinson[MVP]
 
The table has a transaction id number.
I try it on a Report and it works fine

is there a way to do it on a query or form?


Thnks for your help



John Vinson said:
Hi,

I have a table with the folling data (Where R means Received, and S means
Shipped)

TranType Partid Qty
R W293 3
R W293 2
S W293 3
S W293 2

I need to createa a 4th column with a running sum. something like this


TranType Partid Qty Totals
R W293 3 3
R W293 2 5
S W293 3 2
S W293 2 0


Thank you All

Bre-x
Click to expand...

The totals should NOT be stored in any table; they can be computed "on
the fly" in a Query or a Report. However, you will need some
additional field to sort the transactions - perhaps a Date/Time field
defaulting to Now() - so that Access can determine in what order to do
the summing. A Table has no usable order by itself.

If you want to display the Totals on a report, simply put a textbox on
the report with a Control Source

=[Qty] * IIF([TranType] = "R", 1, -1)

and set the Running Sum property of that textbox to Over Group. Set
the Report's GroupBy to group on PartID.

John W. Vinson[MVP]
Click to expand...
 
The table has a transaction id number.
I try it on a Report and it works fine

is there a way to do it on a query or form?
Click to expand...

Yes... but it's considerably less efficient, since you need to call a
slow DSum() or subquery on every single row. Try a Query like

SELECT TranType, Partid, Qty, DSum("[Qty] * IIF([Trantype] = "R", 1,
-1)", "YourTableName", "[PartID] = '" & [PartID] & "' AND
TransactionID <=" & [TransactionID]) AS RunningSum
FROM yourtable
ORDER BY PartID, TransactionID;


John W. Vinson[MVP]
 
Back
Top