Well I've posted questions in the past trying to get a query to add a cumulative sum field within a query.
Cap1 always can do this in 1 query.... It takes me 3 queries to get to the same result but at least I was able to FINALLY get there.
I'm attaching a db only has 4 tables and the 3 queries. The 4 tables have maybe 50 total records.
The ultimate result that I need is what is in qry_CumulSum_step3 but to get there I had to do qry_CumulSum first then qry_CumulSum_step2.
My concern here is when I open qry_CumulSum_step3 it takes too long for there only being 31 records.
So.... what will happen when I have 5,000 or 10,000 records...........
Any suggestions to speed this process up would be appreciated.
My code for the step3 query 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])) AS y
FROM qry_CumulSum_Step2 INNER JOIN qry_CumulSum_Step2 AS a ON (qry_CumulSum_Step2.BusDay = a.BusDay) AND (qry_CumulSum_Step2.ShiftID = a.ShiftID);