Loop through two similar tables highlight field value changes in rows.

[algomez2011]

First post. Hi all!

I have two tables both with identical column structure (110 columns). TableA has old records and TableB CAN have updated records or not. They have the same key in both tables. Field types are the same in both tables and there are null values in both.


TableA:
ID | F1 | F2 |.....| F110 |
1 | A | B |.....| C |

TableB:
ID | F1 | F2 |.....| F110 |
1 | Z | X |.....| C |

Output needed:

TableC:
ID | F1 | F2 |.....| F110 |
1 | A | B |.....| C |
1 | Z | X |.....| C |

So show the same "ID" for old and updated record and then highlight the fields that changed as I export this to excel.

I'm thinking worrying about creating TableC first then tackling the export/highlight thing after would probably be wiser.

I'm pretty much learning as I go.

Help! Thanks

 

[MajP]

Table C is easy to do using a Union query. You can google for examples. I think the second part can be done in Excel using conditional formatting. I have not tried it yet, but I think you could set up a condition that compares the row above and see if the column value has changed but the id is the same.

 

[algomez2011]

OKay, I made a query that would output the rows and fields like I wanted. It lists the rows which have changes (showing only two fields for brevity):

SELECT tblA.*
FROM tblA RIGHT JOIN tblB ON tblA.ID = tblB.ID
WHERE
tblA.[Field2] <> tblB.[Field2] OR
tblA.[Field3] <> tblB.[Field3]
UNION ALL SELECT tblB.*
FROM tblA RIGHT JOIN tblB ON tblA.ID = tblB.ID
WHERE
tblA.[Field2] <> tblB.[Field2] OR
tblA.[Field3] <> tblB.[Field3]
ORDER BY tblA.ID, tblA.ID;

This gives me:

ID | F2 | F3 |.....| F110 |
1 | A | B |.....| C |
1 | Z | X |.....| C |


Now comes the part where I need to use this query and somehow do the highlighting. How can I go through each row/column and highlight the columns? For example I would need to highlight under F2, the A nd Z columns, and under F3, the B and X columns. If no changes just leave as is.

Is there a way to to this in a report using conditional formatting? How would I tell it to look at the row below and compare it to the field above? If not the same then highlight both.

Or if it's easier to just generate the excel sheet directly with the required formatting?

Thanks for the help guys!





which can then generate the excel and it will keep the highlighting? I can also just go straight into generating the Excelsheet.

 

[algomez2011]

oops sorry for the extra line at the bottom..

 

[MajP]

If your planning to export anyways then do the highlighting in Excel. It would be easier. You can probably use conditional formatting in excel to do it.

 

[MichaelRed]

but why is it being sent to excel?

surely it can only be for review / information? so doesn't an Access report provide the same utility?




MichaelRed

 

[MajP]

Sure you could do this in Access, but I think for the user it would be easier in Excel. I think the user would have to write a custom function something like this.

Public Function isSame(someField, PK) as boolean
dim rs as dao.recordset
dim tempVal as variant
set rs = currentdb.openRecordset("Select " & someField & " from someTable)

rs.findfirst "somePK = " & PK
tempVal = rs.fields(someField)
if not rs.bof
isSame = (tempVal = rs.fields(someField))
end if
end function

then in each fields getting conditional formatting

expression is:
isSame("SpecificfieldName",[PKField])

 

[algomez2011]

MichaelRed - but why is it being sent to excel?

surely it can only be for review / information? so doesn't an Access report provide the same utility?"

- Ideally I would like to provide both an Access Report and Excel export. The thing with the report is how can I handle 110 columns? With Excel you can scroll. But yes, I would still like to try both ways if possible. Thanks for your response.

MajP - the users, I'm afraid, would have no clue how to do that.
Is there a way to implement this code through Access VBA so this would habppen in the background automatically before the excel sheets gets generated? You mentioned this was possible via Access, can you give me an Idea how?

I'm currently thinking it would be best to have two tables, one with the old records and one with the updated records instead of using the TableC output above. They would have a one to one relationship.

I would then open two record sets and loop through each row and column and compare which fields have changed.

The part I'm stuck with is how to keep track of which columns have changed so that excel will know to highlight them? Any help here or ideas would be great.

Here's my test code so far which basically tells me which columns are equal or different:

-------------------------------------------
Dim MyDB As DAO.Database

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim lngRS1 As Long
Dim lngRS2 As Long

lngRS1 = 0
lngRS2 = 0

Set MyDB = CurrentDb()
Set rs1 = MyDB.OpenRecordset("tblA", dbOpenSnapshot, dbOpenForwardOnly)
Set rs2 = MyDB.OpenRecordset("tblB", dbOpenSnapshot, dbOpenForwardOnly)

Do While Not rs1.EOF

For i = 0 To rs1.Fields.Count - 1

'If Column Values are not equal
If rs1.Fields(i).Value <> rs2.Fields(i).Value Then
Debug.Print rs1.Fields(i).Value & " <> " & rs2.Fields(i).Value
Else
'Column values are equal
Debug.Print rs1.Fields(i).Value & " = " & rs2.Fields(i).Value
End If

Next
rs1.MoveNext
rs2.MoveNext
Loop

rs1.Close
rs2.Close

Set rs1 = Nothing
Set rs2 = Nothing
-------------------------------------------------

Generates:
001 = 001
Bobby <> Bob
Mike = Mike
002 = 002
Alex = Alex
Zoe = Zoe

Thanks all for the comments. I appreciate you taking your time to help me.

 

[MajP]

If this was me I think I would do it like this. Build tblChanges

tblChanges
ID
fldName (name of field)
fldIndex (index number of the field)
oldVal (the old value)
newVal (the new value)
valType (data type, date, long, text, memo etc)

Now loop through the the records and fields. Every time you find a change log it in the table.

Export to excel
Using tblChanges you know every record and field that changes. The fldindex can be used to identify the column.
Read through tblChanges and hilite the records (now excel rows) and fields (now excel columns)

You can do this in Access, and if you done this work you can still use the tblChanges. On the detail onprint event, check your table and hilite the changed records.

Any chance you could post a few records?
Are you guaranteed to have 2 records per id?
In your union you need to add a field to identify if it is the new record or the old record.

SELECT tblA.*, "Old" as RecordType
.....
Union Select
tblB.*, "New" as RecordType
....

 

[algomez2011]

MajP, thanks for the idea!

Q1: Any chance you could post a few records?

There are 108 columns so it's hard to post but here is a small sample (I added RecordType as you specified):

tblA (NID is key):
RecordType | Department | DName | Title | Ri | NID
Old | 00094 | CNDS | AssoC | F | 0001
Old | 00094 | CNDS | SrAssoc| N | 0002

tblB (NID is key):
RecordType | Department | DName | Title | Ri | NID
New | 00094 | CNDS | SrAssoc| N | 0001
New | 00094 | CNDS | JrAssoc| | 0002


Q2: Are you guaranteed to have 2 records per id? Yes


"Now loop through the the records and fields. Every time you find a change log it in the table."

I take it I can keep working with my test code above. Can you give me an idea for the code for this?

I'll get to the excel export/highlighting once I have this working.

Again thanks!

 

[MajP]

let me take a look at it this evening and provide some code.

 

[algomez2011]

Thank you!

 

[MajP]

Here is the code to write to a table the
record ID
the field name
and the index (position) of the field
This should be enough for conditional formatting or highlighting in excel. With conditional formatting on a report you can build a function that returns true or false. The function would accept the field name and record id. It would return true if it exists in the change table. Or in a report on the print event you loop the fields to see if they exist in the change table.


Now I have included the skeleton and some unused functions that would be used for also writing the NewValue, old value, and type of value. This gets more complicated because your insert query has to account has to account for '',##, and Nulls. So in this case instead of an insert query it would be a lot easier to open the change table as a recordset and write to it. I will take a look at that because as a user I want to be able to not just see that there is a change but what it is.

Public Sub findChanges()
  Const oldData = "tblOldData"
  Const newData = "tblNewData"
  Const PK_ID = "ID"
  Const changeTable = "tblChanges"
  
  Dim rsOld As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Dim fld As DAO.Field
  Dim oldValue As Variant
  Dim newValue As Variant
  Dim ID As Long
  Dim fldName As String
  Dim fldPosition As Integer
  Dim fldType As Long
  Dim generalType As String
  Dim strFields As String
  Dim strValues As String
  Dim strInsert As String
  Dim strSql As String
  
  Set rsOld = CurrentDb.OpenRecordset(oldData, dbOpenSnapshot)
  Set rsNew = CurrentDb.OpenRecordset(newData, dbOpenSnapshot)
  CurrentDb.Execute ("delete * from " & changeTable)
  'strFields = insertFields("recordID", "fldName", "fldPosition", "oldVal", "newVal", "valtype")
  strFields = insertFields("recordID", "fldName", "fldPosition")
  strFields = "Insert into " & changeTable & " " & strFields
  Debug.Print strInsert
  Do While Not rsOld.EOF
     ID = rsOld.Fields(PK_ID)
     For Each fld In rsOld.Fields
       oldValue = fld.Value
       fldName = fld.Name
       fldPosition = fld.OrdinalPosition
       fldType = fld.Type
       generalType = getGeneralType(fldType)
       rsNew.FindFirst PK_ID & " = " & ID
       If Not rsNew.NoMatch Then
          newValue = rsNew.Fields(fld.Name).Value
       End If
       If oldValue <> newValue Then
         strInsert = strFields & " values " & insertValues(ID, sqlTxt(fldName), fldPosition)
         Debug.Print strInsert
         'Select Case generalType
         '  Case "Text"
         '  Case "Numeric"
         '  Case "DateTime"
         '  Case "Boolean"
         'End Select
         CurrentDb.Execute strInsert
       End If
     Next fld
     rsOld.MoveNext
  Loop
End Sub


Public Function getGeneralType(dbType As Long) As String
  'Purpose is to decide how to format the value in a sql search
  'Single quotes, #date#, or no quotes
  
  'Returns the following
  ' Text
  ' Numeric
  ' DateTime
  ' Boolean
  
  'These are the constants
   'dbBigInt Big Integer
   'dbBinary Binary
   'dbBoolean Boolean
   'dbByte Byte
   'dbChar Char
   'dbCurrency Currency
   'dbDate Date / Time
   'dbDecimal Decimal
   'dbDouble Double
   'dbFloat Float
   'dbGUID Guid
   'dbInteger Integer
   'dbLong Long
   'dbLongBinary Long Binary (OLE Object)
   'dbMemo Memo
   'dbNumeric Numeric
   'dbSingle Single
   'dbText Text
   'dbTime Time
   'dbTimeStamp Time Stamp
   'dbVarBinary VarBinary

  Select Case dbType
    Case dbText, dbChar, dbMemo
      getGeneralType = "Text"
    Case dbNumeric, dbSingle, dbDouble, dbLong, dbCurrency, dbBinary, dbBigInt, _
         dbByte, dbGUID, dbInteger, dbLongBinary, dbVarBinary
      getGeneralType = "Numeric"
    Case dbDate, dbTime
      getGeneralType = "DateTime"
    Case dbBoolean
      getGeneralType = "Boolean"
    End Select
End Function
Public Function insertFields(ParamArray varfields() As Variant) As String
  Dim fld As Variant
  For Each fld In varfields
    If insertFields = "" Then
      insertFields = "([" & fld & "]"
    Else
      insertFields = insertFields & ", [" & fld & "]"
    End If
  Next fld
  If Not insertFields = "" Then
    insertFields = insertFields & ")"
  End If
End Function
Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function
Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function
Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[algomez2011]

Majp, I will soak this in and play with it. I'll get back to this thread afterwards. Again, I truly appreciate the help. No way I could of even come close to this!

Alex

 

[MajP]

The first function is really all you need for writing to the table the record, field, and field position. Now you have a persistent set of changes and that can be used in many approaches.

The remainder of the functions are parts for the second part of also saving the actual changes. Although just a little more info it really adds a lot more complexity.

Here is the demo. Run the findChanges procedures and the tblChanges populates correctly.

http://www.4shared.com/document/zcyreQGz/SaveChanges.html

 

[MajP]

This now writes to all of the fields in the change table
the record ID
the changed field name
the field index or position (good for formatting in excel)
the old value
the new value
and the type of value text, numeric, datetime, or boolean

By changing the constants this should work with any two tables or queries

Public Sub findChanges()
  Const oldData = "tblOldData"
  Const newData = "tblNewData"
  Const PK_ID = "ID"
  Const changeTable = "tblChanges"
  
  Dim rsOld As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Dim fld As DAO.Field
  Dim oldValue As Variant
  Dim newValue As Variant
  Dim ID As Long
  Dim fldName As String
  Dim fldPosition As Integer
  Dim fldType As Long
  Dim generalType As String
  Dim strFields As String
  Dim strValues As String
  Dim strInsert As String
  Dim strSql As String
  
  Set rsOld = CurrentDb.OpenRecordset(oldData, dbOpenSnapshot)
  Set rsNew = CurrentDb.OpenRecordset(newData, dbOpenSnapshot)
  CurrentDb.Execute ("delete * from " & changeTable)
  strFields = insertFields("recordID", "fldName", "fldPosition", "oldVal", "newVal", "valtype")
  strFields = "Insert into " & changeTable & " " & strFields
  Do While Not rsOld.EOF
     ID = rsOld.Fields(PK_ID)
     For Each fld In rsOld.Fields
       oldValue = fld.Value
       fldName = fld.Name
       fldPosition = fld.OrdinalPosition
       fldType = fld.Type
       generalType = getGeneralType(fldType)
       rsNew.FindFirst PK_ID & " = " & ID
       If Not rsNew.NoMatch Then
          newValue = rsNew.Fields(fld.Name).Value
       End If
       If oldValue <> newValue Then
           Select Case generalType
           Case "Text"
              strInsert = strFields & " values " & insertValues(ID, sqlTxt(fldName), fldPosition, sqlTxt(oldValue), sqlTxt(newValue), sqlTxt("TEXT"))
           Case "Numeric"
              strInsert = strFields & " values " & insertValues(ID, sqlTxt(fldName), fldPosition, sqlTxt(oldValue), sqlTxt(newValue), sqlTxt("Numeric"))
           Case "DateTime"
              strInsert = strFields & " values " & insertValues(ID, sqlTxt(fldName), fldPosition, SQLDate(oldValue), SQLDate(newValue), sqlTxt("DateTime"))
           Case "Boolean"
               strInsert = strFields & " values " & insertValues(ID, sqlTxt(fldName), fldPosition, sqlTxt(oldValue), sqlTxt(newValue), sqlTxt("Boolean"))
         End Select
         Debug.Print strInsert
         CurrentDb.Execute strInsert
       End If
     Next fld
     rsOld.MoveNext
  Loop
End Sub

 

[algomez2011]

Majp, I should mention all the fields are either type number or text. The PK is text for example.

This app basically imports two excel sheets, the old and new. The data types where assigned automatically on importing, I reckoned.


I'm playing with the code now and trying to troubleshoot a data type mismatch error. error says,

"data type mismatch criteria in expression"

Here: rsNew.FindFirst PK_ID & " = " & ID

these are the mouseover values in debugging:
PK_ID = "NID"
ID = "0001"

 

[MajP]

Based on your example I assumed a numeric PK

rsNew.FindFirst PK_ID & " = '" & ID & "'

 

[algomez2011]

Wow, you rock. I'm going to play with this code until I get it working. I'll keep you posted.

 

[MajP]

This demo fixes a problem. It was not finding when one of the fields was null and one was not.

http://www.4shared.com/document/TXfccEmz/SaveChanges_v2.html