Compare consecutive records in Access

[mservice]

Hi there, newbie to Access, I would normally use cursors in SQL Plus to do this type of thing, however I have been tasked with modifying an access 2000 application and am stuck on the VB code/query to perform the following actions.

I have a table RESULTS which has the following structure;

ASSET    RESULT      DATE
310          Normal         12/03/2007
310          Warning       07/04/2007
310          Warning       17/05/2007
310          Normal         03/06/2007
310          Warning       04/07/2007
310          Normal         03/08/2007
311          Warning       12/03/2007
311          Warning       05/04/2007
311          Normal         16/05/2007
311          Normal         03/06/2007
...
 

I am trying to find the maximum number of consecutive records (based on date) for each individual ASSET where the RESULT is WARNING, which for the above example would be 2 for 310 and 2 for 311 but am having real trouble. Any help would be appreciated as I really don't know where to start.

[Rabbit]

I think you might be able to do it with a really complex query but would be a lot easier with VBA. Do you know any VBA?

[Rabbit]

Well, since you're new to Access, you probably don't know any VBA.
This will be a two-query process, one to "rank" the records and the other to find the max. The second part is easy enough to do by yourself since you already have experience with SQL.

SELECT x.Asset, x.Result, x.Date, iif(x.Result='Warning',((SELECT Count(*) FROM Table2 WHERE Asset = x.Asset AND Result = 'Warning' AND Date < x.Date AND (SELECT TOP 1 Result FROM Table2 WHERE Asset = x.Asset AND [Date] < x.Date ORDER BY [Date] DESC;) = 'Warning';) + 1), 0) AS Rank
FROM Table2 AS x;
 

[Rabbit]

Here's the two queries combined into one (even though it seems as you won't be coming back to see this.):

SELECT Asset, Max(Rank) AS MaxOfRank
FROM (SELECT x.Asset, x.Result, x.Date, iif(x.Result='Warning',((SELECT Count(*) FROM Table2 WHERE Asset = x.Asset AND Result = 'Warning' AND Date < x.Date AND (SELECT TOP 1 Result FROM Table2 WHERE Asset = x.Asset AND [Date] < x.Date ORDER BY [Date] DESC) = 'Warning') + 1), 0) AS Rank FROM Table2 AS x) AS y
GROUP BY Asset;
 

[mservice]

Thanks Rabbit, sorry about the late reply, I have been on vacation (yay). I do know a little VB but I'll have a look at the script and see how I get on. Thanks again for your help!

[mservice]

OK tried the SQL statement but got the following error;

At most one record can be returned by this subquery. (Error 3354)

I have tried calling a function to loop through the data and increment a counter but it doesn't seem to work, here is the code;

 Public Function ConsecutiveRecs(ByVal strComponent As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim strExceptionLevel As String
    Dim strAssetComponent As String
    Dim strSQL As String
    Dim intCounter As Long
 
 
    ' If IsNull(str) Then Exit Function
 
    intCounter = 0
    strExceptionLevel = "Initial"
    strAssetComponent = "New"
 
    strSQL = "SELECT Asset,Result, Date from RESULTS WHERE Asset'" & _
        strComponent & "' ORDER BY Asset, Date;"
 
 
Set rs1 = Nothing
    With RS
        '----------- Open recordset to get records
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
 
        '--------loop through retrieved records
        While Not .EOF
 
 
            If strAssetComponent = ![Asset] And strExceptionLevel = ![Result] Then
                 intCounter = intCounter + 1
                    ConsecutiveRecs = intCounter
            '---- respond with the count
            Else
                If strAssetComponent <> ![Asset] Then _
                   intCounter = 0
                ConsecutiveRecs = intCounter
            '---- reset the counter and respond with the count
 
            End If
            '---store current asset and component combination and exception level to compare with the next one (see above)
            strExceptionLevel = ![Result]
            strAssetComponent = ![Asset]
            .MoveNext
 
        Wend
        .Close
    End With
 
    Set RS = Nothing
 
End Function

I am calling this from the following query;

SELECT DISTINCT Asset , Result, Date, ConsecutiveRecs(Asset) AS Consecutives
FROM RESULTS
GROUP BY Asset, Result, Date;

The result of this is an error, INVALID USE OF NULL at the line strExceptionLevel = ![Result] any ideas?

[Rabbit]

The SQL error is because you have an Asset that has multiple records with the same date.

The second error is because strings can not be null, you'll have to use the Nz() function to convert it to an empty string first.