Count of Consecutive Integers 1

[teach314]

hi to all

I have a table as shown below. ID1 is an autonumber field. ID2 increases by 1 after each 6 records. For each ID1,ID2 combination, there are 7 values of Val listed in increasing order. The 7 values of Val are taken from the integers 1 to 12 with no repeats.

ID1   ID2    Val
================
 1    104     2
 2    104     3
 3    104     7
 4    104     8
 5    104     9
 6    104    10

 7    105     1
 8    105     2
 9    105     5
10    105     7
11    105     9
12    105    10

13    106     1
14    106     3
15    106     5
16    106     7
17    106     9
18    106    12
etc...

I need output that shows, for each ID1,ID2 combination, the MAXIMUM number of CONSECUTIVE values of Val. For the example above, the output should be...

ID2       MaxConsecCount
========================
104           4          (from the 7, 8, 9, 10)
105           2          (from either the 1, 2  or the  9, 10)
106           0

Thanks in advance for any help.
Teach314

 

[dhookom]

I would expect you could set up a DAO recordset and step through it counting consecutive numbering by ID2. Do you need assistance with setting up a function for this?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

Hello again Duane - if it's not asking too much, I would appreciate help with the function code.

 

[dhookom]

You could create a function in a standard module and save the module with the name "modIOweDuane"

Public Function CountConsec(strTable As String, strGroupField As String, _
        intGroupVal As Integer, strValField As String) As Integer
    Dim intCount As Integer
    Dim intMaxCount As Integer
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    Dim intPrevVal As Integer
    
    Set db = CurrentDb
    strSQL = "SELECT [" & strValField & "] FROM [" & strTable & "] WHERE [" & _
        strGroupField & "] = " & intGroupVal & " ORDER BY [" & strValField & "]"
    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)
    intCount = 1
    intPrevVal = -1
    With rs
        Do Until .EOF
            If .Fields(strValField) = intPrevVal + 1 Then
                intCount = intCount + 1
                If intCount > intMaxCount Then
                    intMaxCount = intCount
                End If
             Else
                intCount = 1
            End If
            intPrevVal = .Fields(strValField)
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    CountConsec = intMaxCount
End Function

Then create a query with SQL of:

SELECT ID2, CountConsec("tblTeach314b","ID2",[ID2],"Val") AS CountIt
FROM tblTeach314b
GROUP BY ID2, CountConsec("tblTeach314b","ID2",[ID2],"Val");

Of course you need to change the table name. Maybe next time you ask a question you could provide actual names.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

Thanks for a great solution!
I was able to produce the same output, but in a slower and much more round-about way. I INNER JOINED my table to a copy of itself that was offset by 1 row, took ABS of the differences in the Val values from each component table, used code to introduce a Rank field that would increment by one whenever this difference stayed at 1 (hence consecutive), GROUPED on ID2 using MAX(Rank), etc.. Your code is much more direct and could be more readily used in other contexts. Thanks!!

 

[teach314]

hi Duane - your solution to this problem worked perfectly. But, for my own learning, I have a question.

You used the following SQL...

SELECT ID2, CountConsec("tblTeach314b","ID2",[ID2],"Val") AS CountIt
FROM tblTeach314b
GROUP BY ID2, CountConsec("tblTeach314b","ID2",[ID2],"Val");

You used CountConsec("tblTeach314b","ID2",[ID2],"Val") in the GROUP BY statement. But, Each value of ID2, after GROUPing, can only admit one value of CountConsec("tblTeach314b","ID2",[ID2],"Val"), so GROUPing on this expression doesn't seem necessary. Was this expression used in the GROUP BY statement to avoid using an AGGREGATE function on the same expression in the SELECT statement? Just wonderin'

 

[dhookom]

I expect you could use First, Min, Max, or something else but I believe you need to use something in the totals.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016