Finding the count of consecutive values using two criteria.

[JGalletta]

Id like to find the count of consecutive like values in set of records:

In recording pesticide applications, each chemical has a type and a group number. The type can either be pesticide fungicide or herbicide (simple for this example) and the group can range from 1-5 (also dumbed down). I'd like to be able to count (for each type) the amount of consecutive records with the same group number. So if I record the following data:

Pesticide, 2
Pesticide, 2
Herbicide, 2
Pesticide, 2

The amount of consecutive pesticide type 2 records is 3.

Also if I record the following:

Pesticide,2
Pesticide,3
Pesticide,2
Pesticide,2

The amount of consecutive pesticide type 2 records is 2.

And for these:

Pesticide, 2
Herbicide, 3
Fungicide, 4
Pesticide, 2

The amount of consecutive pesticide type 2 records is 2.

How can I acquire these values using vba or sql methods? - I only want one value for each set of criteria (type,group) that I inquire about.

I hope someone can understand what I'm trying to ask here. Feel free to ask for more details, I'll be back here after my lunch nap (1hr from now).

Thanks in advance,

John

 

[tehNellie]

It sounds like a GROUP BY should do it

SELECT type, group, count(typeID)
FROM tblTypes
GROUP BY type, group

 

[JGalletta]

Won't that just give sums of each type and group combination? I need the count to end when a different group in the same type is introduced. Perhaps I'll just loop through a recordset containing the list of data (sorted from current date to earliest date) and count records that match criteria, and as soon as a member of the same type but different group is observed exit the loop and report the counter value. Sounds good, right? This will return the value of the count of the most recent set of consecutive group/type combo specified.

 

[tehNellie]

Sounds like a complicated way of doing a GROUP BY and COUNT to me

What you do with the GROUP BY depends on the operator you [don't] use.

GROUP BY basically says to the database "lump everything in this table together where these columns contain the same values". What you do with that afterwards depends on the operator you use.

If you use none, you get a list of distinct values:

Pesticide, 2
Herbicide, 2

To use your first sample.

So in my original example I told the Database to COUNT() the ID values in the those groups so you'd have got:

Type,       Group, CountofID
Pesticide,   2,    3
Herbicide,   2,    1

Maybe you have a price column to reflect that you can buy the same thing from different places. So you could use AVERAGE(price) on that column

Maybe you only care about groups and types that occur more than once, in which case you can filter your results using the HAVING statement;

SELECT type, group, count(typeID)
FROM tblTypes
GROUP BY type, group
HAVING count(typeID) >1

In a GROUP BY, a standard WHERE statement filters your data before the group takes place. HAVING filters your results after the GROUP BY has occurred.

 

[spikepl]

What do you mean by "consecutive"? There is not much "consecutive" in your examples, if consecutive means in sequence.

 

[tehNellie]

doh, think I misread part of the intention, the original examples threw me a bit. In which case you're right, a simple GROUP BY isn't going to give you the right answer.

can you elaborate a little on what it is this output gives you?

 

[JGalletta]

Ok I hope you're ready to see what the heck I've been working on... And I hope I'm ready to be ridiculed for doing something far too complicated for no reason.

Here's what I have now.. Working on syntax, and hasn't been successfully tested, but you'll see the general gist of what I'm trying to do.

        Dim mySQL8 As String
        mySQL8 = "SELECT tblPesticides.[IRAC Group].Value As ChemGroup FROM tblPesticides INNER JOIN (tblTankMixes INNER JOIN (tblApplicationRecord INNER JOIN tblFieldApps ON tblApplicationRecord.[Application Number] = tblFieldApps.[Application Number]) ON tblTankMixes.[Application Number] = tblApplicationRecord.[Application Number]) ON tblPesticides.[Common Name] = tblTankMixes.[Common Name] WHERE (((tblFieldApps.Field)= '" & F & "') AND ((tblApplicationRecord.[Date of Application])=#" & Doa & "#) AND ((tblApplicationRecord.Sprayed)=-1) AND ((tblPesticides.[Chemical Type].Value) ='" & DLookup("tblPesticides.[Chemical Type]", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'") & "'))"
        
        Set rs8 = db.OpenRecordset(mySQL8)
        If rs8.RecordCount < 1 Then GoTo Line1
        
        rs8.MoveFirst
        Dim GroupCount As Integer
        Dim ChemGroup As String
        GroupCount = 0
        Do While Not rs8.EOF
            ChemGroup = rs8![ChemGroup]
            If ChemGroup = DLookup("tblPesticides.[IRAC Group].Value", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'") Then
                GroupCount = GroupCount + 1
            Else
                Exit Do
            End If
            rs8.MoveNext
        Loop
        
        If GroupCount + 1 > 2 Then
            Me.WarningText = Me.WarningText & Chr(13) & Chr(10) & CN & " Resistance Warning:  The application of " & CN & " in field " & F & " will violate pest group resistance guidelines.  Consider using a chemical that is not in group " & DLookup("tblPesticides.[IRAC Group].Value", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'") & "."
            WarningCount = WarningCount + 1
        End If
Line1:

It's disgusting - I know. Was hoping for an answer before I had to hard head it. Or maybe I'm even doing it right?.. who knows.

 

[JGalletta]

Just remembered, I need to sort the recordset descending by date...

 

[spikepl]

Can't you explain in words (or better, by example) what you want? Your examples in #1 do not fit with my interpretation of "consecutive", if you intended to count "max number of given type of records that directly follow one another"

 

[JGalletta]

All pesticide labels have a warning like this: Do not spray more than two consecutive applications of a Group 3(group) Pesticide(type) without first applying a Pesticide (same type as type mentioned earlier in this sentence) from a different Group.

I need to warn the user if they are breaking this rule.

Applying a fungicide(different type) and then a pesticide(same type) from the same group will still break this rule - The rule applys within type only - spraying a different type does not make the next spray of the same type count as non consecutive. (This is where the word consecutive comes into play although it is irrelevant to the procedures used to acquire values regarding 'consecutive' values.)

 

[JGalletta]

Bump:: Anyone have any input?

 

[spikepl]

Is the example in #1 100% consistent with what you wrote? Because your prose is not unambiguous, so a numerical example is better.

 

[spikepl]

In any case I have to run off soon.

Provided you are looking for max sequential identical records, I would consider making a function and call it in a query in a calculated field. It increases the count of sequential rows with the same GroupID. It can surely be refined. And then you could find max and get your number

Something along those lines (untested)

Public Function MyCount(GroupId)  AS Integer
   Static oldID as Integer 'static preserves the value till next round
Static tempCount
If GroupdID=0 Then  ' initialize the thing by calling it somewhere in code, prior to running the query
       tempCount=0
       oldID=0
End if
If oldID=GroupID Then
        tempCount=tempCount+1
else
        tempCount=0
end if

oldID=GroupID

End if

MyCount=tempCount+1

End function

 

[JGalletta]

Your example is just what I need, but rather than the count of all of the same Group IDs, I need just the count of (and here's that word again) consecutive like values iff the beginning value is equal to the group of the chemical in the record currently being tested.

For example, here are a list of group numbers returned when a recordset is generated from a query that asks for all of the group numbers of past records sorted in descending date order, when prompted for a chemical type (e.g. Pesticides or Herbicides). Group number is not specified in this query but will be part of the counting. For a new record applying a group 3 chemical, I'd like to count the bold values

3
3
2
3
4
5
3
3
3
3
2
2
2
3
5
5
6

So these are all the same type of chemical. I'd like the code to store a variable representing the sum of the first set of consecutive group 3 chemicals in this list. (the answer is 2)

If the value of the first group number is not equal to the chemical group being tested, the chemical pasts the test, and no flag is raised for example:

If I'm spraying a Group 3 Pesticide (Bold is the group and underlined is the Type) My query of all pesticide groups that have been recorded returns this list of groups.

2
3
3
4
3
3
2
2
1

The program should return a 0 and exit the counting loop since the last group recorded was a 2 (descending order time-wise), so after the current chemical application is recorded the list will then look like this:

3
2
3
3
4
3
3
2
2
1

And the count would then be 1.


I'm as frustrated in attempting to describe this as you are in attempting to decode my examples... I understand.

 

[spikepl]

I have to go and can only suggest you play with my example - you can put any logic in it you want, and you can expand the function to take more than one argument. The trick is the Static declaration, that remembers the value on the next call ( ie, from record to record).

The alternative is to open the recordset (or recordsets) and put the logic into code there, as you have attempted.

 

[JGalletta]

Thanks for your time!

 

[JGalletta]

Here is what the final working code looks like:

        If Len(DLookup("tblPesticides.[IRAC Group].Value", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'")) > 0 Then
            Dim mySQL8 As String
            mySQL8 = "SELECT tblPesticides.[IRAC Group].Value As ChemGroup1, tblApplicationRecord.[Date of Application] FROM tblPesticides INNER JOIN (tblTankMixes INNER JOIN (tblApplicationRecord INNER JOIN tblFieldApps ON tblApplicationRecord.[Application Number] = tblFieldApps.[Application Number]) ON tblTankMixes.[Application Number] = tblApplicationRecord.[Application Number]) ON tblPesticides.[Common Name] = tblTankMixes.[Common Name] WHERE (((tblFieldApps.Field)= '" & F & "') AND ((tblApplicationRecord.[Date of Application]) <= #" & Doa & "#) AND ((tblApplicationRecord.Sprayed)=-1) AND ((tblPesticides.[Chemical Type].Value) ='" & DLookup("tblPesticides.[Chemical Type]", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'") & "')) ORDER BY tblApplicationRecord.[Date of Application] DESC"
            
            Set rs8 = db.OpenRecordset(mySQL8)

            Dim GroupCount As Integer
            Dim ChemGroup As String
            Dim ChemGroupCompare As String
            GroupCount = 0
            If rs8.RecordCount < 1 Then GoTo Line1 Else
                
                rs8.MoveFirst
                Do While Not rs8.EOF
                    ChemGroup = rs8![ChemGroup1]
                    ChemGroupCompare = DLookup("tblPesticides.[IRAC Group].Value", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'")
        
                    If ChemGroup = ChemGroupCompare Then
                        GroupCount = GroupCount + 1
                    Else
                        Exit Do
                    End If
                    rs8.MoveNext
                Loop
Line1:
            If GroupCount > 1 Then
                Me.WarningText = Me.WarningText & Chr(13) & Chr(10) & CN & " Resistance Warning:  The application of " & CN & " in field " & F & " will violate pest group resistance guidelines.  Consider using a chemical that is not in group " & DLookup("tblPesticides.[IRAC Group].Value", "tblPesticides", "tblPesticides.[Common Name] = '" & CN & "'") & "."
                WarningCount = WarningCount + 1
            End If
        End If