Dsum on a query (vs on a table) - doable?

[peskywinnets]

I've never played with Dsum before, but struggling.

Basically I have a table....but it has a lot of unwanted data ( for want of a better word...'noise'), so I use a query filter the data & show only what I need (& in the format I need) via a query (called query1`), perfect thus far here's the output I get from my query1....



....now all I want to have is a running total for the column called amount (e.g. the running total on row 3 would be 14.99), but access seems to be baulking (I've tried lots of permutations)

Or does Dsum only work with tables?

 

[MarlaC]

DSum won't get you a running total but a total per grouped field (Payment Type, presumably). You might want to look into using a totals query with a subdatasheet, though I'm not sure that will get you exactly what you want.

 

[pbaldy]

Yes, a query can be used in the table argument. The syntax would otherwise be the same, so if you have a DSum() that works against the table it should work on the query.

 

[pbaldy]

DSum won't get you a running total but a total per grouped field (Payment Type, presumably).

It could give a running total as long as there's a field or fields it can use to order on, like an ID field or something.

 

[peskywinnets]

It could give a running total as long as there's a field or fields it can use to order on, like an ID field or something.

I have an Amazon Order ID column (which I didn't show for confidentiality purposes) it's essentially a text data type ....could I therefore order on that & achieve a running total?

 

[pbaldy]

Sounds like it, yes, as long as that's the order you want the running sum in.

 

[CJ_London]

your code would be something like

 SELECT [Payment Type], Amount, DSum("Amount","myTable","[OrderID]<=" & OrderID) AS RunningSum
 FROM myTable
 ORDER BY OrderID

or probably faster

 SELECT [Payment Type], Amount, (SELECT Sum(Amount) FROM myTable WHERE OrderID<=myTable.OrderID) AS RunningSum
 FROM myTable
 ORDER BY OrderID