DSUM Function Criteria

[Guest]

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.

 

[Duane Hookom]

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().

 

[Guest]

Hey there

I have the same problem 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]

Thanks for the suggestion, but unfortunately I can not use a report - as much
as I'd like to it won't accomplish my objective, so I'm trying to find a
query to work from.

I think I get your idea of a replacement field. Could I just create another
field and set it to autonumber and use that? My concern with that is whether
I have to consider sequential order. Rather, is there some way to just have
the query run automatically do the running total based on the specific record
and its position in the query results?

If this isn't possible, what would be the best way to achieve this result
without using a report?

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.

 

[Duane Hookom]

There isn't really anything such as "position in the query results" without
defining a field or fields to sort by. You could add an autonumber field if
you weren't picky about the order of the records.

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


--
Duane Hookom
MS Access MVP
--

Thanks for the suggestion, but unfortunately I can not use a report - as
much
as I'd like to it won't accomplish my objective, so I'm trying to find a
query to work from.

I think I get your idea of a replacement field. Could I just create
another
field and set it to autonumber and use that? My concern with that is
whether
I have to consider sequential order. Rather, is there some way to just
have
the query run automatically do the running total based on the specific
record
and its position in the query results?

If this isn't possible, what would be the best way to achieve this result
without using a report?

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.