running sum of qty field in access query grid

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.

ex
SELECT TblBalance.Student_FK, TblBalance.Qty, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.ProdNum, TblBalance.InvNo
FROM TblBalance
GROUP BY TblBalance.Student_FK, TblBalance.Qty, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.ProdNum, TblBalance.InvNo
HAVING (((TblBalance.Student_FK)=23))
ORDER BY TblBalance.EventDate, TblBalance.ProdNum;

Open in new window


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
FROM TblBalance
WHERE (((TblBalance.Student_FK)=23));

Open in new window

PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeterBaileyUkAuthor Commented:
Ive managed to get a running sum finally

SELECT TblBalance.B_ID, TblBalance.Qty, DSum("Qty","Query24","B_ID<=" & [B_ID]) AS CreditRemaining, TblBalance.Student_FK, TblBalance.Type
FROM TblBalance
WHERE (((TblBalance.Student_FK)=23))
ORDER BY TblBalance.B_ID;

Open in new window


I used query24 to select only the records for student of interest in this case was studentid 23.

So Ive done the first part of the question, the second part was how do I now get the block of eventdates from the current zero balance to the last cr payment

ex2
0
PeterBaileyUkAuthor Commented:
I think I will write a function to grab the dates. as Ive used query 24 here to filter for specific student on tblbalance is it possible to do all this within one query without having to refer to query 24?
0
Gustav BrockCIOCommented:
I would just loop the records as shown.
When Qty is positive (or InvNo not Null), pick the start date; when it happens again, the previous date is the end date, and the current date is the new start date.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PeterBaileyUkAuthor Commented:
Ok Ive got the recordset as here:

ex
here is my code to traverse that stops at the null point as you suggested

With RstBalanceData

    Do While Not RstBalanceData.EOF
    
    If IsNull(.Fields("InvNo")) Then
        If Index = 0 Then
            StrDates = .Fields("English_Name").Value & " " & .Fields("EventDate").Value & ", "
            Debug.Print StrDates
        Else
            StrDates = StrDates & .Fields("EventDate").Value & ", "
            Debug.Print StrDates
        End If
        
    
    Else
    
    getDateString2 = StrDates
    Exit Function
    End If
    
    
    Index = Index + 1
    .MoveNext
    Loop

End With

Open in new window


the dates need to be in order so I wonder if  I can store in an array the date and its matching qty as I loop through then reverse the array to create the string.

How do I create the array for these two fields

currently it produces:
Wendy 23/01/2018,
Wendy 23/01/2018, 18/01/2018,
Wendy 23/01/2018, 18/01/2018, 16/01/2018,
Wendy 23/01/2018, 18/01/2018, 16/01/2018,

it should be form : "Wendy 16/01/2018, 18/01/2018, 23/01/2018"

I think I am close now
0
Gustav BrockCIOCommented:
Isn't it just sorting asc and not desc for the event date?
0
Jeffrey CoachmanMIS LiasonCommented:
Coming to the party late here.

So I am not clear on the exact output required.

Here is my take on this:
Use a report to get the running sum(s).
(This way I did not have to reverse the sort order to get the last classes)
Then accumulate the dates in a variable, for each record after the Payment.
I have simply displayed the desired text (Variable) in a message box, ...You can do whatever you need with the final string...

Even if this is not what you wanted exactly, ...perhaps you could see how I got the string to display properly, and apply that same logic to your loop code...

Sample attached

JeffCoachman
Database42.accdb
0
PatHartmanCommented:
I'm not sure this is the most effective way to do this.  Shouldn't you look at the existing balance as you add a new transaction and see what the effect is?  It seems that you are prepared to let the balance go negative.  I don't know your business rules so I can't say whether I agree with that or not.  You can produce a current balance weekly or monthly or whatever makes sense, you can identify the accounts that are either overdrawn or close to it by simply summing the credits and debits and send warnings via email or printed reports.

Then if you want to see a  running balance, do it as a report.
0
PeterBaileyUkAuthor Commented:
Ok finally I resolved it by taking the last zero balance eventdate and filtering the running sum query on that.

Public Function getDateString(StudentID As Integer) As String
Dim StrQuery As String
Dim db As DAO.Database
Set db = CurrentDb
Dim StudentIDIN As Integer
Dim RstBalance As DAO.Recordset
Dim StrOutput As String
Dim index As Integer
Dim EngName As String
Dim CreditCounter As Integer
Dim RCount As Long
Dim Group As String
Dim qd As DAO.QueryDef
Dim qd2 As DAO.QueryDef

Dim newSQLQd1 As String
Dim newSQLQd2 As String
Dim newSQLQd3 As String

Dim StrQryLastZeroDate As String
Dim RstLastZeroDate As DAO.Recordset
StudentIDIN = StudentID
Dim DtDateZero As Date
Dim DtTrue As Boolean

Dim StrDt As String
Dim SumCredits As Long
Dim SumCredTrigger As Boolean

newSQLQd1 = "SELECT TblBalance.B_ID, TblBalance.Qty, TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblBalance.ProdNum, TblBalance.Link_FK" _
& " FROM TblBalance" _
& " WHERE (((TblBalance.Student_FK) =" & StudentIDIN & "))" _
& " ORDER BY TblBalance.B_ID;"

Set qd = db.QueryDefs("QryRunningCreditDataSetByStudent")
qd.SQL = newSQLQd1


newSQLQd2 = "SELECT TblBalance.B_ID, TblBalance.Qty, DSum('Qty','QryRunningCreditDataSetByStudent','B_ID<=' & [B_ID]) AS CreditRemaining, TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblStudents.English_Name, TblClassSessions.Group" _
& " FROM (TblBalance INNER JOIN TblStudents ON TblBalance.Student_FK = TblStudents.Student_ID) INNER JOIN TblClassSessions ON TblBalance.Session_FK = TblClassSessions.Session_ID" _
& " GROUP BY TblBalance.B_ID, TblBalance.Qty, DSum('Qty','QryRunningCreditDataSetByStudent','B_ID<=' & [B_ID]), TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblStudents.English_Name, TblClassSessions.Group" _
& " HAVING (((TblBalance.Student_FK) =" & StudentIDIN & "))" _
& " ORDER BY TblBalance.B_ID;"





Set qd2 = db.QueryDefs("QryRunningCredit")
qd2.SQL = newSQLQd2




StrQryLastZeroDate = "SELECT QryRunningCredit.B_ID, QryRunningCredit.Qty, QryRunningCredit.CreditRemaining, QryRunningCredit.EventDate, QryRunningCredit.Student_FK, IIf([CreditRemaining]='0',1,0) AS ZeroBalance" _
& " FROM QryRunningCredit" _
& " WHERE (((QryRunningCredit.Student_FK) = " & StudentIDIN & ") And ((IIf([CreditRemaining] = '0', 1, 0)) = 1))" _
& " ORDER BY QryRunningCredit.EventDate DESC;"
'Debug.Print StrQryLastZeroDate
Set RstLastZeroDate = db.OpenRecordset(StrQryLastZeroDate, dbOpenDynaset)

If RstLastZeroDate.BOF And RstLastZeroDate.EOF Then

'no date filter
DtTrue = False
Else
DtTrue = True
With RstLastZeroDate
    .MoveFirst
    DtDateZero = .Fields("EventDate").Value
    StrDt = .Fields("EventDate").Value
DtDateZero = Format(DtDateZero, "mm/dd/yyyy")
End With

End If

If DtTrue = False Then
newSQLQd3 = "SELECT TblBalance.B_ID, TblBalance.Qty, DSum('Qty','QryRunningCreditDataSetByStudent','B_ID<=' & [B_ID]) AS CreditRemaining, TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblStudents.English_Name, TblClassSessions.Group" _
& " FROM (TblBalance INNER JOIN TblStudents ON TblBalance.Student_FK = TblStudents.Student_ID) INNER JOIN TblClassSessions ON TblBalance.Session_FK = TblClassSessions.Session_ID" _
& " GROUP BY TblBalance.B_ID, TblBalance.Qty, DSum('Qty','QryRunningCreditDataSetByStudent','B_ID<=' & [B_ID]), TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblStudents.English_Name, TblClassSessions.Group" _
& " HAVING (((TblBalance.Student_FK) =" & StudentIDIN & "))" _
& " ORDER BY TblBalance.B_ID;"
Else

newSQLQd3 = "SELECT TblBalance.B_ID, TblBalance.Qty, DSum('Qty','QryRunningCreditDataSetByStudent','B_ID<=' & [B_ID]) AS CreditRemaining, TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblStudents.English_Name, TblClassSessions.Group" _
& " FROM (TblBalance INNER JOIN TblStudents ON TblBalance.Student_FK = TblStudents.Student_ID) INNER JOIN TblClassSessions ON TblBalance.Session_FK = TblClassSessions.Session_ID" _
& " GROUP BY TblBalance.B_ID, TblBalance.Qty, DSum('Qty','QryRunningCreditDataSetByStudent','B_ID<=' & [B_ID]), TblBalance.Student_FK, TblBalance.Type, TblBalance.EventDate, TblBalance.Session_FK, TblBalance.InvNo, TblStudents.English_Name, TblClassSessions.Group" _
& " HAVING (((TblBalance.Student_FK) =" & StudentIDIN & ") AND ((TblBalance.EventDate)>#" & DtDateZero & "#))" _
& " ORDER BY TblBalance.B_ID;"

Debug.Print newSQLQd3
End If


Dim x As Long

Set RstBalance = db.OpenRecordset(newSQLQd3, dbOpenDynaset)


If RstBalance.BOF And RstBalance.EOF Then

Else

With RstBalance
.MoveLast
x = .RecordCount
.MoveFirst
Do While Not RstBalance.EOF
CreditCounter = .Fields("Qty").Value
SumCredits = SumCredits + Abs(.Fields("Qty").Value)

    If CreditCounter = -1 Then
            StrOutput = StrOutput & Format((.Fields("EventDate").Value), "dd/mm") & ", "
        Else
            Group = .Fields("Group").Value
            StrOutput = StrOutput & Format((.Fields("EventDate").Value), "dd/mm") & " x " & Abs(CreditCounter) & " " & Group & ", "
       
    End If
'        Debug.Print StrOutput

   



.MoveNext
Loop

End With
StrOutput = StrOutput & " Total Credits: " & SumCredits
getDateString = StrOutput
   Debug.Print StrOutput
End If

End Function

Open in new window

0
PeterBaileyUkAuthor Commented:
I went with this as it was the key to getting the final date string.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.