Compare Data on Tables for Change in Value (1 Viewer)

[woodsy74]

I am not sure if my question should be posted here or under queries but I thought I'd give this a shot. I'm new to the forum and by new means an expert with Access but I can follow directions and usually tweak other folks' stuff to get it to work for me. Anyway, I have two tables (Current and Previous) in the db that capture pipeline activity. The tables have the same columns (OrderNum, Amt, Status, Cancel_Date) in each. I am trying to find any OrderNum that has a change in value on any of the columns between the two tables and I want to state which column(s) changed.

I don't need to find OrderNums that are on Current table and not on the Previous table (or vice versa). Only OrderNums that are on both tables but with different values in any column.

As an example of the data on the tables:
Current table has OrderNum 1, Amt 100, Status A, Cancel_Date Null
Current table has OrderNum 2, Amt 100, Status I, Cancel_Date Null
Current table has OrderNum 3, Amt 100, Status A, Cancel_Date Null
Previous table has OrderNum 1, Amt 125, Status A, Cancel_Date Null
Previous table has OrderNum 2, Amt 100, Status I, Cancel_Date Null
Previous table has OrderNum 3, Amt 100, Status I, Cancel_Date Null

I am looking for an end result of:
OrderNum 1 Change in Amt
OrderNum 3 Change in Status

I would not want to return OrderNum 2 because there was no change in any column.
Also, if multiple columns changed on a particular OrderNum then I would like to be able to list out each column that changed (Change in Amt, Cancel Date).

Each of my tables will have about 25,000 rows on it. And, while I only listed out 4 columns there are more like 12 (and more will be added as folks request them).

Is this something that can be done via Code or a Query? Let me know if you have any questions. Thank you for your time!!!

 

[Isskint]

A couple of convoluted queries will achieve this.

Query 1 "WoodsyCompare" SQL

SELECT WoodsyCurrent.OrderNum, DLookUp("[Amt]","[WoodsyPrevious]","[OrderNum]='" & [OrderNum] & "'")=[Amt] AS Expr1, DLookUp("[Status]","[WoodsyPrevious]","[OrderNum]='" & [OrderNum] & "'")=[Status] AS Expr2, DLookUp("[CancelDate]","[WoodsyPrevious]","[OrderNum]='" & [OrderNum] & "'")=[CancelDate] AS Expr3
FROM WoodsyCurrent
WHERE (((DLookUp("[Amt]","[WoodsyPrevious]","[OrderNum]='" & [OrderNum] & "'")=[Amt])=False)) OR (((DLookUp("[Status]","[WoodsyPrevious]","[OrderNum]='" & [OrderNum] & "'")=[Status])=False)) OR (((DLookUp("[CancelDate]","[WoodsyPrevious]","[OrderNum]='" & [OrderNum] & "'")=[CancelDate])=False));

This does the comparison bit for you and will only return entries where Amt, Status or CancelDate have changed.

Query 2 "WoodsyComapre Query" SQL

SELECT WoodsyCompare.OrderNum, IIf([Expr1]=False,"Change in Amt","") & IIf([Expr2]=False,IIf([Expr1]=False,IIf([Expr3]=False,", "," and "),"") & "Change in Status","") & IIf([Expr3]=False,IIf([Expr1]=False Or [Expr2]=False," and ","") & "Change in CancelDate","") AS Expr4
FROM WoodsyCompare;

This checks the result of the first query and builds a text string of the changes.

You could use this second query as a datasource for a report or a form.


Another option would be to create a table - tblCompare - that you could write to through VBA code, such as;

Sub CompareTables()
On Error GoTo CompareTablesErr
Dim con, TblA, TblB As Object
Dim strSql, strMsg As String
    strSql = "Delete tblChanges.Change FROM tblChanges;" 'delete current data in changes table
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True
    Set con = Application.CurrentProject.Connection
    Set TblA = CreateObject("ADODB.Recordset")
    TblA.Open "WoodsyCurrent", con, 1 'create a recordset for Current table
 
    Set TblB = CreateObject("ADODB.Recordset")
    TblB.Open "WoodsyPrevious", con, 1 'create a recordset for Previous table
 
    While (Not (TblA.EOF)) 'check for end of file
        strMsg = ""
        If TblA!amt <> TblB!amt Then
            strMsg = "Order No " & TblA!OrderNum & " change in Amt"
        End If
 
        If TblA!Status <> TblB!Status Then
            If strMsg = "" Then
                strMsg = "Order No " & TblA!OrderNum & " change in Status"
            Else
                If TblA!Canceldate <> TblB!Canceldate Then
                    strMsg = strMsg & ", change in Status"
                Else
                    strMsg = strMsg & " and change in Status"
                End If
            End If
        End If
 
        If TblA!Canceldate <> TblB!Canceldate Then
            If strMsg = "" Then
                strMsg = "Order No " & TblA!OrderNum & " change in CancelDate"
            Else
                strMsg = strMsg & " and change in CancelDate"
            End If
        End If
 
        If strMsg <> "" Then
            strSql = "INSERT INTO tblChanges ([Change]) VALUES ('" & strMsg & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSql
            DoCmd.SetWarnings True
        End If
 
        TblA.MoveNext
        TblB.MoveNext
    Wend
 
CompareTablesExit:
    Set con = Nothing
    Set TblA = Nothing
    Set TblB = Nothing
    Exit Sub
CompareTablesErr:
    MsgBox Err.Description & vbLf & Err.Number
    GoTo CompareTablesExit
End Sub

 

[woodsy74]

Thanks for your reply.

The query pieces seem to work very well except wtih Nulls. The queries are not recognizing when a column changes from or to Null. Is there any way to account for changes to/from Null?

I also played around with the code you provided for making the tblChanges. When I loaded 200 records into each table this method was much faster than the queries. This seems to be the better route for me since I will have about 20,000 records per table. However, I ran into a couple issues that I would need to tweak:

1. This method also didn't account for Null changes.

2. Could I output to tblChanges in 2 columns - one with OrderNum and the other with Changes? As is, everything gets output to the one column.

3. When OrderNums varied between the 2 tables I ran into issues. The change would be found and accounted for correctly but it would be assigned to the incorrect OrderNum. For example if Current has OrderNums 1, 2, & 3 while Previous has 1 & 3 the output table will assign the change from OrderNum 3 to OrderNum 2 (even though that doesn't exist in Previous).

Again, thanks for your help thus far. Let me know if you can assist with my 3 items regarding the code. I'd like to go the code route becuase it seems so much faster than the query route.

Let me know if you have any questions.

 

[Isskint]

Yeah i thought the codeing path would be better.


OK, so issues.

1. That is a simple case of including a check for this in the logical test, such as

   If (TblA!CancelDate <> TblB!CancelDate) Or (TblA!CancelDate & "" <> TblB!CancelDate) Or (TblA!CancelDate <> TblB!CancelDate & "") Then

2. You can output however YOU want. You could have fields: OrderNum, ChangeIn, OldValue, NewValue. When you then get more than 1 change in 1 ordernum you would get multiple records. OR OrderNum, Change to record the changes as now (EG Change in AMT and CancelDate). Let me know which or play around yourself.
3. Now this was something i had not considered. I assumed (big mistake, H U G E :banghead::bangheadeach table would have the same OrderNums. If this is not the case then it would just mean adjusting the code to use Dlookup() as a logical test for an ordernum. Would you want the fact that an OrderNum has been erased recorded as a change? Let me know and i will adjust the code accordingly.

 

[woodsy74]

For the output table I'd like to have two columns. One with OrderNum and the other with Changes. Changes justs needs to list out the columns that had a value change (including Null changes). I do not need to capture the value of the change, just the column name.

I do not need to account for OrderNums that are not on both tables. I only need to evaluate OrderNums that are in both.

Let me know if you have any other questions. Thanks again for your assistance!

 

[Isskint]

OK, so code looks like

Sub CompareTables()
On Error GoTo CompareTablesErr
Dim con, TblA, TblB As Object
Dim strSql, strMsg As String
    strSql = "DELETE tblChanges.OrderNum, tblChanges.Change FROM tblChanges;" 'delete current data in changes table
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True
    Set con = Application.CurrentProject.Connection
    Set TblA = CreateObject("ADODB.Recordset")
    TblA.Open "WoodsyCurrent", con, 1 'create a recordset for Current table
 
    Set TblB = CreateObject("ADODB.Recordset")
    TblB.Open "WoodsyPrevious", con, 1 'create a recordset for Previous table
 
    While (Not (TblA.EOF)) 'check for end of file
        strMsg = ""
        If DCount("[OrderNum]", "[WoodsyPrevious]", "[OrderNum]='" & TblA!OrderNum & "'") > 0 Then 'check OrderNum in TblA is in TblB
            TblB.MoveFirst 'Move pointer to start of recordset
            While TblB!OrderNum <> TblA!OrderNum 'step through TblB until TblA!OrderNum is found
                TblB.MoveNext
            Wend
 
            If (TblA!amt <> TblB!amt) Or (TblA!amt & "" <> TblB!amt) Or (TblA!amt <> TblB!amt & "") Then
                strMsg = "Change in Amt"
            End If
 
            If (TblA!Status <> TblB!Status) Or (TblA!Status & "" <> TblB!Status) Or (TblA!Status <> TblB!Status & "") Then
                If strMsg = "" Then
                    strMsg = "Change in Status"
                Else
                    If (TblA!CancelDate <> TblB!CancelDate) Or (TblA!CancelDate & "" <> TblB!CancelDate) Or (TblA!CancelDate <> TblB!CancelDate & "") Then
                        strMsg = strMsg & ", change in Status"
                    Else
                        strMsg = strMsg & " and change in Status"
                    End If
                End If
            End If
 
            If (TblA!CancelDate <> TblB!CancelDate) Or (TblA!CancelDate & "" <> TblB!CancelDate) Or (TblA!CancelDate <> TblB!CancelDate & "") Then
                If strMsg = "" Then
                    strMsg = "Change in CancelDate"
                Else
                    strMsg = strMsg & " and change in CancelDate"
                End If
            End If
 
            If strMsg <> "" Then
                strSql = "INSERT INTO tblChanges ([OrderNum],[Change]) VALUES ('" & TblB!OrderNum & "', '" & strMsg & "');"
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSql
                DoCmd.SetWarnings True
            End If
        End If
 
        TblA.MoveNext
 
    Wend
 
CompareTablesExit:
    Set con = Nothing
    Set TblA = Nothing
    Set TblB = Nothing
    Exit Sub
CompareTablesErr:
    MsgBox Err.Description & vbLf & Err.Number
    GoTo CompareTablesExit
End Sub

Just to check the process is what you need, this;

• Steps through each record of Current table
• Checks OrderNum is in Previous
• Checks for changes - including ""
• Records change to OrderNum from Previous by field(s) changed

 

[woodsy74]

The process you list out is what I am looking for and during my tests of this new code it appears to be working but I do have 2 issues/questions:

If the CancelDate column is a Date field then does something special need to be used in order to evaluate that field properly? Currently if there is any value in that column then it is being returned as a change (even though both tables have the same date, 7/25/2012 for example). If the column is Null in both tables then it works fine.

Evevrything else so far looks good. I was able to add a new column to the comparison logic and that worked out fine (I have to add about 10 more columns).

Besides the Dates, my only other question would be with speed. I ran a few tests using about 20 rows or so and it worked great (my 20 row examples didn't have values in both tables for CancelDate so I didn't notice this issue yet). I then decided to load up more realistic data so I had 20,000 rows in each table. The tables had the same data and I manually changed a handful of them so I would know what changes I was expecting to find. Also, all fields were populated, I had no Null values.

When I ran this through it took about 90 minutes to complete. Now, I don't know if it took so long because every row came back as a change due to the CancelDate issue or it took this long because of the time a day I am running it (it will usually be run during off hours) or if this is just how long it will take when I have this many rows (plus I still need to add more columns).

Let me know your thoughts on doing the comparison with a Date field and what you think about how long the code should take to run.

Thanks again for all your help. I really appreciate it.

 

[Isskint]

the speed issue will not change i suspect. certainly the less network activity the quicker you could expect it to run, but with that many records i do not think that much - you could look at the syscmd to display a progress meter so people know how far the comparison has got.

I wondered about the date display myself. only 2 things i can think of, have a date column in tblChanges or convert the date to a string as dd mmmm yyyy, that way it would be easier to understand and conversion back to a date (if required) would be easier.

I am away from a proper computer (i phone is good, but it dont run Access) for a couple of days, but will look at this if you have no success.

 

[woodsy74]

Changing the CancelDate to a Text field did the trick.

I went back to the very 1st section of code you provided and started playing with it. This code seems to run much, much quicker. The biggest issue was the difference in OrderNums but I can work around that by creating another Current and another Previous table that only containing matching OrderNums and then run the code against these tables instead of the originals.

So far I've been able to update the original code to output the way the latest version did. And, I was able to add some additional columns. Now, I just need to put everything together.

Thanks for all of your help on this.