DSum subtotaling

[Guest]

Hey there

I have the same problem as below and me monkeying around isn't helping me,
could you maybe give me an example using the example that's in this
discussion?

Thanx
Ian

If you are doing this for a report, you are much better off creating your
running sum in a text box. If not in a report, you need to identify a
field/value that can replace OrderID in the where clause of your DSum().

--
Duane Hookom
MS Access MVP

I'm writing a DSUM expression with the hope of calculating a running total
with each record. My current DSUM expression is providing a running total
based on the OrderID rather than each line. Here's what I'm currently
getting with the DSUM expression listed for the table named "Order
Details":

Running Total: (DSum("[Order Details].[UnitPrice]*[Order
Details].[Qty]","Order Details","[OrderID] <= " & [Order
Details].[OrderID]))

OrderID Qty UnitPrice TotalLine Running Total
10248 12 14 168 474
10248 10 9.80 98 474
10248 2 17.00 34 474
10248 5 34.80 174 474
10249 9 18.60 167.40 2337.4
10249 40 42.40 1696 2337.4

Instead, I need the DSUM expression to give a running total by line (not
grouped by OrderID) like:

Running Total
168
266
300
474
641
2337.4
2414.4

How do I modify the criteria to accomplish this?

Thanks.

 

[Guest]

If you have a line number that would be helpful. With a line number you
could add to the statement:

Running Total: (DSum("[Order Details].[UnitPrice]*[Order
Details].[Qty]","Order Details","[OrderID] <= " & [Order
Details].[OrderID] and "[LineNumb] <="& [Orderdetails].[LineNumb]))

to be honest I have to fiddle with the dsum function sometimes as I do not
use it all the time (I cannot guarantee my statement is correct and do not
have the time to replicate but I think you can grab the idea). The problem
here is that you are asking for the running sum of an order # and need to
distinguish each line as well. The only way to do so would be to add the
line #. I assume that you are using sales orders, so each order should have
a line number.

Hey there

I have the same problem as below and me monkeying around isn't helping me,
could you maybe give me an example using the example that's in this
discussion?

Thanx
Ian

If you are doing this for a report, you are much better off creating your
running sum in a text box. If not in a report, you need to identify a
field/value that can replace OrderID in the where clause of your DSum().

--
Duane Hookom
MS Access MVP

I'm writing a DSUM expression with the hope of calculating a running total
with each record. My current DSUM expression is providing a running total
based on the OrderID rather than each line. Here's what I'm currently
getting with the DSUM expression listed for the table named "Order
Details":

Running Total: (DSum("[Order Details].[UnitPrice]*[Order
Details].[Qty]","Order Details","[OrderID] <= " & [Order
Details].[OrderID] and "[LineNumb] <="& [Orderdetails].[LineNumb]))

OrderID Qty UnitPrice TotalLine Running Total
10248 12 14 168 474
10248 10 9.80 98 474
10248 2 17.00 34 474
10248 5 34.80 174 474
10249 9 18.60 167.40 2337.4
10249 40 42.40 1696 2337.4

Instead, I need the DSUM expression to give a running total by line (not
grouped by OrderID) like:

Running Total
168
266
300
474
641
2337.4
2414.4

How do I modify the criteria to accomplish this?

Thanks.