compare previous row with next

[binway]

Hi Team
I have some data that changes and gets an update date whenever a field is changed.
I am trying to report when a row has changed.
I can get pretty close if I take out the update date using a group by in conjunction with a dedup query but am not convinced I am getting the correct values for the changed row for
update date and updatedby as I had to use the "last" function in the group by query.
Some sample data is below.
ID Name Update_Date Updated by Status Glob_App_Class Simp_act_ret_date Simp_class Display
9117 App 2014-02-10 11:12:18 714 Act Inter_Plat Maintain
9117 App 2014-02-28 17:45:07 118 Act Inter_Plat Strategic Y
9117 App 2014-03-15 07:02:14 859 InAct Invest Strategic Y
9117 App 2014-03-15 08:39:03 859 InAct Invest Strategic
9117 App 2014-03-19 00:51:52 880 Act Dep Strategic Y
9117 App 2014-03-28 09:15:58 714 Act Dep Retire Y
9117 App 2014-03-31 12:43:27 118 Act Inter_Plat Deprecated/Retire Y
113 Bridge 2014-02-20 10:52:36 252 Act Dep Invest
113 Bridge 2014-02-28 17:44:16 118 Act Dep Strategic Y
113 Bridge 2014-03-07 01:00:03 system Act Dep Strategic
113 Bridge 2014-03-07 09:01:16 system InAct Dep Strategic Y
113 Bridge 2014-03-08 09:01:13 system Act Inter_Plat Strategic/Invest Y

Currently I have the output as a spreadsheet format but ideally want it in the format below .

ID Name Update_Date Updated by Status Field Old Val New Val
9117 App 2014-02-28 17:45:07 118 Act Simp_Class Maintain Strategic
9118 App 2014-03-15 07:02:14 119 InAct Glob_app_Class Inter_Plat Invest
9117 App 2014-03-19 00:51:52 880 Act Glob_app_Class Invest Dep

The code I have thus far gets me to read the records and move next, I have had a couple of iterations of the code where it is now looking at a union query but it is the concept of comparing changes in field for a record that I can't get my head around.

Dim db As Database
Dim rsupdate As Recordset

Dim workingid As String
Dim workingName As String
Dim workingupdatedby As String
Dim workingupdated As String
Dim workingFieldNme As String
Dim workingFieldVal As String
Dim nxtworkingid As String
Dim nxtworkingName As String
Dim nxtworkingupdatedby As String
Dim nxtworkingFieldNme As String
Dim nxtworkingupdated As String
Dim nxtworkingFieldVal As String




Set db = CurrentDb
Set rsupdate = db.OpenRecordset("qApp Change His", dbOpenDynaset)

rsupdate.MoveLast
rsupdate.MoveFirst


workingid = ""
workingName = ""
workingupdatedby = ""
workingupdated = ""
workingFieldNme = ""
workingFieldVal = ""
nxtworkingid = ""
nxtworkingName = ""
nxtworkingupdatedby = ""
nxtworkingFieldNme = ""
nxtworkingupdated = ""
nxtworkingFieldVal = ""



Do While Not rsupdate.EOF
workingid = rsupdate!AssetID
workingName = rsupdate!Asset_Name
workingupdatedby = rsupdate!updatedby
workingupdated = rsupdate!Updated
workingFieldNme = rsupdate!FieldName
workingFieldVal = rsupdate!FieldVal



If workingid <> rsupdate!AssetID And workingupdated <> rsupdate!Updated Then
workingid = ""
workingName = ""
workingupdatedby = ""
workingFieldNme = ""
workingupdated = ""
rsupdate.MoveNext
End If

If workingid = rsupdate!AssetID And workingupdated = rsupdate!Updated Then
rsupdate.MoveNext
nxtworkingid = rsupdate!AssetID
nxtworkingName = rsupdate!Asset_Name
nxtworkingupdatedby = rsupdate!updatedby
nxtworkingFieldNme = rsupdate!FieldName
nxtworkingFieldVal = rsupdate!FieldVal
nxtworkingupdated = rsupdate!Updated

If rsupdate!AssetID = nxtworkingid Then
If workingstatus <> nxtworkingstatus Then
ChangedField = "Status"
OLDVal = workingstatus
NewVal = nxtworkingstatus



'rsupdate.MoveNext
End If
End If
End If
newupdatedby = ""
newupdateby = rsupdate!updatedby
'no need for an else just Insert into
'rsupdate.MoveNext
Loop


End Sub

any help appreciated.

 

[PeteG]

Hi,
Just a thought but might it be easier to loop through the recordset and assign all the values to an array or collection/dictionary of some sort, and then work on that to find changes?

 

[binway]

Thanks for the thought PeteG but I am not much of a genius when it comes to arrays etc
What I did do was create a seperate query for each of the columns I am testing.
eg for column Global App class
SELECT AssetID, Asset_Name, Status, Updated, Updatedby, Own_grp, Own_sub_bus, 'Global_App_Class' AS FieldName, Global_appl_class AS FieldVal
FROM Application Change HistorY
GROUP BY AssetID, Asset_Name, Status, Updated, Updatedby, Owning_group, Owning_sub_business, 'Global_App_Class', Global_appl_class;

Use VBA to check one record to the next and where it doesn't match pump it into a table.
At this point I am creating a seperate piece of code for each query which is not elegant but is working.
I am sure I can make this more efficient but will get it all working first.
This allows me to get the output exactly as the user wants.

Private Sub Global_App_Click()
Dim db As Database
Dim rsupdate As Recordset

Dim workingid As String
Dim workingName As String
Dim workingStatus As String
Dim workingupdatedby As String
Dim workingupdated As String
Dim workingowngrp As String
Dim workingOwnSubBus As String
Dim workingFeldNme As String
Dim workingFeldVal As String

Dim nxtworkingid As String
Dim nxtworkingName As String
Dim nxtworkingStatus As String
Dim nxtworkingupdatedby As String
Dim nxtworkingupdated As String
Dim nxtworkingowngrp As String
Dim nxtworkingOwnSubBus As String
Dim nxtworkingFeldNme As String
Dim nxtworkingFeldVal As String




Set db = CurrentDb
Set rsupdate = db.OpenRecordset("qGlobal_App_Class", dbOpenDynaset)

rsupdate.MoveLast
rsupdate.MoveFirst
' now if you wanted you have a count of how many records you have to loop.
' get variables to hold the values set them to empty for the first run
workingid = ""
workingName = ""
workingStatus = ""
workingupdatedby = ""
workingupdated = ""
workingowngrp = ""
workingOwnSubBus = ""
workingFeldNme = ""
workingFeldVal = ""

nxtworkingid = ""
nxtworkingName = ""
nxtworkingStatus = ""
nxtworkingupdatedby = ""
nxtworkingupdated = ""
nxtworkingowngrp = ""
nxtworkingOwnSubBus = ""
nxtworkingFeldNme = ""
nxtworkingFeldVal = ""

Do While Not rsupdate.EOF

workingid = rsupdate!AssetID
If IsNull(rsupdate!Asset_Name) Then
workingName = "Blank"
Else: workingName = rsupdate!Asset_Name
End If
If IsNull(rsupdate!Status) Then
workingStatus = "Blank"
Else: workingStatus = rsupdate!Status
End If
If IsNull(rsupdate!updatedby) Then
workingupdatedby = "Blank"
Else: workingupdatedby = rsupdate!updatedby
End If
If IsNull(rsupdate!Updated) Then
workingupdated = "Blank"
Else: workingupdated = rsupdate!Updated
End If
If IsNull(rsupdate!Own_grp) Then
workingowngrp = "Blank"
Else: workingowngrp = rsupdate!Own_grp
End If
If IsNull(rsupdate!Own_sub_bus) Then
workingOwnSubBus = "Blank"
Else: workingOwnSubBus = rsupdate!Own_sub_bus
End If
If IsNull(rsupdate!FieldName) Then
workingFeldNme = "Blank"
Else: workingFeldNme = rsupdate!FieldName
End If
If IsNull(rsupdate!FieldVal) Then
workingFeldVal = "Blank"
Else: workingFeldVal = rsupdate!FieldVal
End If

rsupdate.MoveNext

nxtworkingid = rsupdate!AssetID
If IsNull(rsupdate!Asset_Name) Then
nxtworkingName = "Blank"
Else: nxtworkingName = rsupdate!Asset_Name
End If
If IsNull(rsupdate!Status) Then
nxtworkingStatus = "Blank"
Else: nxtworkingStatus = rsupdate!Status
End If
If IsNull(rsupdate!updatedby) Then
nxtworkingupdatedby = "Blank"
Else: nxtworkingupdatedby = rsupdate!updatedby
End If
If IsNull(rsupdate!Updated) Then
nxtworkingupdated = "Blank"
Else: nxtworkingupdated = rsupdate!Updated
End If
If IsNull(rsupdate!Own_grp) Then
nxtworkingowngrp = "Blank"
Else: nxtworkingowngrp = rsupdate!Own_grp
End If
If IsNull(rsupdate!Own_sub_bus) Then
nxtworkingOwnSubBus = "Blank"
Else: nxtworkingOwnSubBus = rsupdate!Own_sub_bus
End If
If IsNull(rsupdate!FieldName) Then
nxtworkingFeldNme = "Blank"
Else: nxtworkingFeldNme = rsupdate!FieldName
End If
If IsNull(rsupdate!FieldVal) Then
nxtworkingFeldVal = "Blank"
Else
nxtworkingFeldVal = rsupdate!FieldVal
End If

If workingid = nxtworkingid And workingFeldVal <> nxtworkingFeldVal Then
db.Execute "INSERT INTO APP_CHANGE_VALUES VALUES(" & workingid & ",""" & workingName & """,""" & workingStatus & """,""" & nxtworkingupdated & """, """ & nxtworkingupdatedby & _
""", """ & nxtworkingowngrp & """, """ & nxtworkingOwnSubBus & """, """ & nxtworkingFeldNme & _
""", """ & workingFeldVal & """, """ & nxtworkingFeldVal & """);"

ElseIf workingid = nxtworkingid And workingFeldVal = nxtWorkfeldval Then

End If


Loop


End Sub

 

[TheAceMan1]

I saw this thread on Monday, just couldn't get to it until now. What follows is a technique that should get you where you want to go. It uses [blue]two recordsets[/blue] (the 2nd is a clone of the first). rsA starts off at the 1st record. rsB the 2nd record. Now its a matter of single stepping thru the records and parsing the fields for comparison. Since we don't want to parse all fields, [blue]a string of fieldnames to be skipped[/blue] is setup. Any fieldnames not is this string will be compared.

I myself would transfer the entire record once any field doesn't compare. So you may want to modify the Insert section. Also ... I was going to transpose and substitute your field names, but your naming convention is just a little too long for me. In any case the code follows ...

[blue]   Dim db As dao.Database, SQL As String, fldSkip As String
   Dim rsA As dao.Recordset, rsB As dao.Recordset
   Dim fld As Field, fldNam As String
   
   Set db = CurrentDb
   SQL = "SELECT VacID, NameID, Typ, vacDate " & _
         "FROM tblHours;"
   Set rsA = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rsA.BOF Then
      rsA.MoveLast
      rsA.MoveFirst [green]'@ 1st record[/green]
      
      Set rsB = rsA.Clone
      rsB.MoveLast
      rsB.AbsolutePosition = 1 [green]'@ 2nd record[/green]
      
      fldSkip = "vacID-NameID-vacDate"
      
      Do
         For Each fld In rsA.Fields
            fldNam = fld.Name
            
            If InStr(1, fldSkip, fldNam) = 0 Then [green]'field in skip string?[/green]
               If rsA(fldNam) <> rsB(fldNam) Then [green]'fields match?[/green]
                  [purple]'SQL = Your Insert SQL
                  'db.Execute SQL, dbFailOnError[/purple]
                  Exit For
               End If
            End If
         Next
         rsA.MoveNext
         rsB.MoveNext
      Loop Until rsB.EOF
   End If

   Set rsB = Nothing
   Set rsA = Nothing
   Set db = Nothing[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[binway]

Thanks AceMan1
Haven't tested the code yet but the concepts are far more elegant than mine.
Will give it a whirl in due course.