MS Access SQL query – Count records until value is met

---

I have an Access query (qr1) that returns the following data:

dateField	stringField1	stringField2	booleanField
11/09/20 17:15	John	Nick	0
12/09/20 17:00	John	Mary	-1
13/09/20 17:30	Ann	John	0
13/09/20 19:30	Kate	Alan	0
19/09/20 19:30	Ann	Missy	0
20/09/20 17:15	Jim	George	0
20/09/20 19:30	John	Nick	0
27/09/20 15:00	John	Mary	-1
27/09/20 17:00	Ann	John	-1
27/09/20 19:30	Kate	Alan	0
28/09/20 18:30	Ann	Missy	-1
03/10/20 18:30	Jim	George	-1
04/10/20 15:00	John	Nick	0
04/10/20 17:15	John	Mary	0
04/10/20 20:45	Ann	John	0
05/10/20 18:30	Kate	Alan	0
17/10/20 15:00	Jim	George	0
17/10/20 17:15	John	Nick	0
18/10/20 15:00	John	Mary	-1
18/10/20 17:15	Ann	John	0

Notes:

• The string data may by repetitive or not.
• The date data are stored as string. I use a function to convert it as date.

Public Function STR2TIME(sTime As String) As Date
Dim arr() As String
    sTime = Replace(sTime, ".", "/")
    arr = Split(sTime, "  ")
    STR2TIME = DateValue(Format(arr(0), "dd/mm/yyyy")) + TimeValue(arr(1))
End Function

• qr1 is ORDERED BY STR2TIME(dateField) ASC

---

Now I need to run an extra query that will do the following:

• add an extra column where:
• counts records until yes (-1) on booleanField
• after this, starts over counting by 1

In this case the output should look like this:

dateField	stringField1	stringField2	booleanField	countField
11/09/20 17:15	John	Nick	0	1
12/09/20 17:00	John	Mary	-1	2
13/09/20 17:30	Ann	John	0	1
13/09/20 19:30	Kate	Alan	0	2
19/09/20 19:30	Ann	Missy	0	3
20/09/20 17:15	Jim	George	0	4
20/09/20 19:30	John	Nick	0	5
27/09/20 15:00	John	Mary	-1	6
27/09/20 17:00	Ann	John	-1	1
27/09/20 19:30	Kate	Alan	0	1
28/09/20 18:30	Ann	Missy	-1	2
03/10/20 18:30	Jim	George	-1	1
04/10/20 15:00	John	Nick	0	1
04/10/20 17:15	John	Mary	0	2
04/10/20 20:45	Ann	John	0	3
05/10/20 18:30	Kate	Alan	0	4
17/10/20 15:00	Jim	George	0	5
17/10/20 17:15	John	Nick	0	6
18/10/20 15:00	John	Mary	-1	7
18/10/20 17:15	Ann	John	0	1

---

Problem

I have tried many things all giving wrong numeric results.

Finally I thought that counting the zeros from the current date till the previous (biggest and smaller than the current), would do the trick:

SELECT t.*, (SELECT COUNT(*)
             FROM qr1 tt 
             WHERE booleanField = 0
             AND STR2TIME(tt.dateField) >= (SELECT TOP 1 dateField
                                           FROM qr1
                                           WHERE booleanField = -1
                                           AND STR2TIME(dateField) < STR2TIME(t.dateField)
                                           ORDER BY STR2TIME(dateField) DESC
                                          )
             AND STR2TIME(tt.dateField) <= STR2TIME(t.dateField)
            ) AS CountMatches
FROM qr1 t;

but still gives me wrong numeric results on countField:

countField
0
0
1
2
3
4
5
6
1
2
3
1
12
13
14
15
16
17
18
13

What am I doing wrong? I can’t get it. How to get the desired result?

---

EDIT:

I’m posting the final code, based on @Gordon Linoff ‘s and @Gustav ‘s answers, slightly simplified.

Explanation of changes:

• I got rid of the conversion-function in this step. Instead of converting 7 times * every single record, I convert only once in the first query and here the values are ready to compare.
• I omitted checking the zeros as it was not necessary.
• I added NZ function to get values when the inner subquery returns NULL. That is when there isn’t any yes with smaller date to count from (first records usually).
• The only problem left, was that with NZ I got values 1 less than what I needed, so I added -1 to the dateField to count 1 more.

Here is the code:

SELECT t.*, (SELECT COUNT(*) FROM qr2 tt 
             WHERE tt.dateField <= t.dateField
             AND tt.dateField > NZ((SELECT TOP 1 dateField FROM qr2 
                                    WHERE booleanField = True
                                    AND dateField < t.dateField
                                    ORDER BY dateField DESC
                                    ), tt.dateField - 1) 
            ) AS CountMatches
FROM qr2 AS t;

Answer

This is doable, though a little convoluted:

Select 
    qr1.dateField, 
    qr1.stringField1, 
    qr1.stringField2, 
    qr1.booleanField, 

        (Select Count(*) From qr1 As t1 
        Where 
            (t1.booleanfield = true And t1.dateField = qr1.dateField) 
            Or
            (t1.booleanfield = false And t1.dateField <= qr1.dateField And 
            t1.dateField >= Nz(
                (Select Top 1 dateField From qr1 As t 
                Where t.dateField < qr1.dateField And t.booleanField = True 
                Order By t.dateField Desc), 
                t1.dateField ))) As countField

From 
    qr1;

Output:

You string converter can be replaced by this expression:

TrueDate = CDate(Replace(TextDotDate, ".", "/"))

This you should apply at a much earlier state, like in qr1.