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

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.

EDIT:

Advertisement

Answer