Running Total that resets when there is a zero

[kahmed1985]

Hi, I am trying to produce a field in a query that gives the running total, but then resets if there is a zero.

So for the table below, i would like to produce the field Wins in a Row

Date- Team- Win- Wins in a Row
29-Oct-06- Liverpool- 1- 1
12-Nov-06- Manchester- 1- 1
19-Nov-06- Liverpool- 1- 2
26-Nov-06- Manchester- 0- 0
03-Dec-06- Manchester- 1- 1
10-Dec-06- Liverpool- 0- 0
17-Dec-06- Manchester- 1- 2
24-Dec-06- Liverpool- 1- 1
07-Jan-07- Liverpool- 1- 2
14-Oct-07- Liverpool- 1- 3

I can manage to get a running total but am struggling with the reset. Any ideas would be really helpful, many thanks

 

[plog]

Come on Pat, that's not helpful in a variety of ways. First, I'm not saying your advice is inaccurate (yet), but comments without explanation don't help people learn why things are to be done they way they are to be done. Second, 'Do it in a report' is so vague it isn't really advice he can use.

kahmed, I think the best way to accomplish what you want is with VBA (which means it can easily be used in both reports and queries). Below is the code to use:

Function getWinsInARow(t, d)
    ' determines how many wins in a row have occured from last loss date to current match date (d) for a team (t)

criteria = "[Team]='" & t & "' AND [MatchDate]<=#" & d & "#"
    ' will be the criteria portion of the DCount that gets the actual number of wins in a row
lastloss = DMax("[MatchDate]", "YourTableNameHere", "[Win]=0 AND [Team]='" & t & "' AND [MatchDate]<#" & d & "#")
    ' gets date of loss immediately prior to date (d)
If IsNull(lastloss) = False Then criteria = criteria & " AND [MatchDate]>#" & lastloss & "#"
    ' if there was a loss prior to date (d), amends criteria to count from that loss to the current date
getWinsInARow = DCount("[Win]", "YourTableNameHere", criteria)
         
End Function

In the above code, replace instances of 'YourTableNameHere' with the name of your table that holds the data you posted. Additionally, 'Date' is a bad name for a field in Access because it is a reserved word. I used 'MatchDate'. I suggest you change your field's name to that. If its not possible, change my 'MatchDate's to 'Date'.

I've notated the code so you know whats happen throughout it (notes are lines prefixed with an apostrophe). Now, to use your code create a query using this SQL:

SELECT YourTableNameHere.MatchDate, YourTableNameHere.Team, YourTableNameHere.Win, IIf([Win]=0,0,getWinsInARow([Team],[MatchDate])) AS WinsInARow
FROM YourTableNameHere
ORDER BY YourTableNameHere.MatchDate;

Again, replace 'YourTableNameHere' and I used MatchDate.

 

[kahmed1985]

Thanks a lot for that Plog, I certainly need to use it for a query, and it has worked really well, but not perfectly, some of the rows I'm not getting a value i'm looking for, hopefully it just needs a bit of tweaking

 

[plog]

Can you provide an example? List the source data, what you are getting and what you expect to get.

 

[kahmed1985]

I've figured it out, thanks a lot Plog, there was an issue with the date format, it seems that the #" & d & "# creates a US formatted date, while all my data is UK format, I searched the internet and found lots of people having the same issue and that there is now way around it.

So I tried converting the date into a number using CDbl first(strDate: CDbl([MatchDate]), and then modifying the code to reflect this, and after a bit of trial and error it worked

My final code is as below:

Function getWinsInaRow(T, D)


criteria = "[Team]='" & T & "' AND [strDate]<='" & D & "'"

lastloss = DMax("[strDate]", "Results", "[Won]=0 AND [Team]='" & T & "' AND [strDate]<'" & D & "'")

If IsNull(lastloss) = False Then criteria = criteria & " AND [strDate]>'" & lastloss & "'"

getWinsInaRow = DCount("[Won]", "Results", criteria)


End Function