I have an aggregate query in which I need to have a total and a running total. The query results are not correct. Any assistance would be appreciated.
The ultimate goal is to create an active graph on a form for each metric that will show the performance on discreet data by month but also reflect a running total across the year. The waters get muddy as our fiscal year runs October - September, so I cannot break on clean Dec 31.
The fields are
Query fields are:
MetID (Alias: mID) - An autonumber representing a metric for which daily data is being collected. Grouped, ascending in query column 1
datDate (Alias: dDate) - a shortdate date field representing the day of data entry formatted as format(datDate,'yyyy-mm') and Grouped, ascending in query column 2
SumOfdatPoint - Summed daily data enties Summed in query column 3
RunTot - a dsum expression attempting to create a running total by metric and month. an Expression in query column 4
The dsum expression is:
RunTOT: DSum("datPoint","tbstrdata","metID <= " & [mID] & "AND datDate <= format(#" & [dDate] & "#,'yyyy-mm')")
Here's the underlying SQL:
_________________
SELECT tbStrData.metID AS mID, Format([datDate],'yyyy-mm') AS dDate, Sum(tbStrData.datPoint) AS SumOfdatPoint, DSum("datPoint","tbstrdata","metID <= " & [mID] & "AND datDate <= format(#" & [dDate] & "#,'yyyy-mm')") AS RunTOT
FROM tbStrData
GROUP BY tbStrData.metID, Format([datDate],'yyyy-mm')
ORDER BY tbStrData.metID, Format([datDate],'yyyy-mm');
_________________
The results look like this...the total doesn't start until the subsequent line of each metric group and it's summing on the months instead of the metrics. Any suggestions?
mID dDate SumOfdatPoint RunTOT 25 2020-10 100.00 25 2020-11 72.73 100 25 2020-12 61.10 172.730003356934 25 2021-01 70.37 233.830001831055 26 2020-10 0.06 26 2020-11 0.05 100.059999998659 26 2020-12 0.04 172.840003356338 26 2021-01 0.04 233.980001829565 27 2020-10 84.95