CODEWITHSUNDEEP
ms-accesssumms-access-2010ms-access-2013cumulative-sum
Turniipp
Turniipp

Reputation: 27

Dsum Function in Query Field not working properly

I have a date based query that returns two fields, Week Ending Date and L2N, and I want to add a third field that provides a rolling total of the L2N field. I have tried using DSum as follows:

RunL2N: DSum("[L2N]","Occupied Apts & L2N", "Week Ending Date=" & "'" & [Week Ending Date] & "'")

In the code above, L2N is the field I want to sum, and Occupied Apts & L2N is the query that returns the fields. The query asks for a Week Ending Date and then delivers all of the records that equal or proceed the given Week Ending Date.

It no worky right. My goal is for the RunL2N field to show a rolling total of the L2N field for each record. In other words, if the query returns multiple records, I want it to show the L2N field result, and then show the Run2L2N field, which sums the L2N fields of the records above and the current record.

So if the query returns a 1 in the L2N field, then a 3 for the next record, then a 5 for the next record and lastly a 7 for the final record, I want the RunL2N field to show 1 for the first record, 4 for the next record, 9 for the next record and lastly 16 for the final record.

Upvotes: 1

Views: 815

Answers (1)

HansUp
HansUp

Reputation: 97101

Since the field name includes spaces, bracket it like this: [Week Ending Date]

Assuming it's Date/Time type, use # delimiters before and after the date value.

Finally I think you want to get the sum from rows where [Week Ending Date] <= the date in the current row.

DSum("[L2N]","Occupied Apts & L2N", "[Week Ending Date]<=" & Format([Week Ending Date], "\#yyyy-m-d\#"))

However if you use a correlated subquery, instead of DSum(), to compute the running sum of L2N, you won't have to bother about delimiters for the date value.

SELECT
    y1.[Week Ending Date],
    y1.L2N,
    (
        SELECT Sum(y2.L2N)
        FROM [Occupied Apts & L2N] AS y2
        WHERE y2.[Week Ending Date] <= y1.[Week Ending Date]
    ) AS RunL2n
FROM [Occupied Apts & L2N] AS y1;

Upvotes: 1

Related Questions