I have to get a query that includes a cumulative sum field of TransAmt that starts at zero for each day and accumulates the Day's total for each consecutive ShiftID.
Attached is a stripped down db.
I'm too stupid to figure out how to accomplish this in 1 query so it takes me 3 to get to this point BUT in the qry_CumulSum_Step3 my column or field y: is accumulating it by ShiftID but I tried copying that query to a qry_TEST and adding a criteria for the field [BusDay] (which is my Day field) but I get #Error.
My code is
SELECT qry_CumulSum_Step2.ShiftID, qry_CumulSum_Step2.BusDay, qry_CumulSum_Step2.CashFdActID, qry_CumulSum_Step2.TransAmtTtl, Val(DSum("TransAmtTtl","qry_CumulSum_Step2","ShiftID <=" & [a].[ShiftID] & " AND #[BusDay]# <=#" & [a].[BusDay] & "#")) AS y
FROM qry_CumulSum_Step2 INNER JOIN qry_CumulSum_Step2 AS a ON (qry_CumulSum_Step2.ShiftID = a.ShiftID) AND (qry_CumulSum_Step2.BusDay = a.BusDay);