I have a query that shows credits and debits against a student, in the example here the student paid for 10 credits and used them up. not all classes pertain to one credit here we can see a class used three credits (this means that I cant count in tens or rely on product number or invoice number as they do not correlate). I need to be able to see when the balance gets to zero and then get the class date strings for that parent so the parent can see the dates the credits got used. So I thought if I could get a running sum of qty field in here that would be part of the solution. Then the second part to grab that block of dates. In the example the student ran out then purchased 20 credits and has used 7 credits across 3 sessions so the start date there is 16/01/2018 and the strings build up until the credit reaches zero again.
SELECT TblBalance.Student_FK, TblBalance.Qty, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.ProdNum, TblBalance.InvNo
GROUP BY TblBalance.Student_FK, TblBalance.Qty, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.ProdNum, TblBalance.InvNo
ORDER BY TblBalance.EventDate, TblBalance.ProdNum;
I thought this might help but it isnt working right
SELECT TblBalance.B_ID, TblBalance.Qty, DSum("Qty","TblBalance","B_ID<=" & [B_ID]) AS Expr1, TblBalance.Student_FK, TblBalance.Type