compare field data in two tables

[slimjen1]

All; I inherited a database that has two tables that are structured identical; one called tblcurrentdata and the other called tblpriordata. The user wants a report or export query that only shows records that has differences between prior weeks data and the current weeks data. The tables have 12 fields of data in a record per quote number. The user wants a query or report to identify the QuoteNum and any changes between the prior and current records. The table structures are as follows:

tblCurrentData/tblPriorData:
QuoteNum 
CoName
State
ZipcodeNo
priorityColor
CreateDte
ModifyDte
POC
GrossWges
NetWges
SubsidyAmt
BonusAmt

For ex.:
QuoteNum 12345 field 5 (prioritycolor) changed from red in the prior week to green in the current week and field 7(POC) changed from Scott in the prior week to Jonson in the current week . The user wants to know this.
QuoteNum 23451 did not have any changes therefore does not need to be listed in the query/report
Unmatched query doesn’t work because it does not compare multiple fields. I tried to structure a union query and use <> in each field but got too tedious and didn’t give the expected results. Does anyone know of a simple solution to get these results? Thanks

 

[plog]

You didn't explicitly state it, so let me ask: Is tblcurrentdata what you are calling "this weeks data" and tblpriordata what you are calling "last weeks data"? You want to compare one table to the other and see whats different right?

If that's the case, then create a query using both tables, link them via QuoteNum and for every field you make a calculated field like so:

CoName_Diff: IIf(tblcurrentdata.CoName & "" <> tblpriordata.CoName & "", 1,0)

Then when you run the query, every 1 value indicates a difference between the tables.

 

[slimjen1]

Sorry for the omission. That's exactly correct. I get you solution; but I just been informed they may be adding additional calculated fields in the query used. So my question is; might there be a way to do this in vba; maybe a loop? Else It would be a nightmare to do this for each field especially if there are more than 12 fields.
Thanks

 

[plog]

I don't know about nightmare, but yes you could do this in VBA. The way I'd do it is dynamically create that same query. You would declare an array of all field names you want to check then construct the SQL in the manner I described before:

Here's some pseudo code:

FieldsToCheck = {"CoName", "State", "ZipcodeNo", ...}

strSQL = "SELECT QuoteNum"

Loop through FieldsToCheck array:
   strSQL = strSQL & ", IIf(tblcurrentdata." & FieldToCheck & "'' <> tblpriordata." & FieldToCheck & "'', 1,0) AS " & FieldToCheck & "_Diff"

End Loop

strSQL = strSQL & " FROM tblCurrentdata INNER JOIN...."

Run strSQL

 

[vbaInet]

... I just been informed they may be adding additional calculated fields in the query used.

Do you know what the calculations would be beforehand?

I'm sure that plog won't mind if I copy his pseudocode so using a technique similar to plog's you can do:

CalculationsToAdd = {", [expression] AS Calculation1", ", [expression] AS Calculation2", ...}

strSQL = "SELECT QuoteNum"
strRs = "SELECT TOP 1 * FROM tblcurrentdata;"

Create recordset from strRs

Loop through field names in strRs recordset
    strSQL = strSQL & ", IIf(tblcurrentdata.[" & rs.Fields(x).Name & "] <> tblpriordata.[" & rs.Fields(x).Name & "], 1, 0) AS " & rs.Fields(x).Name & "_Diff"
End Loop

Loop through CalculationsToAdd array:
    strSQL = strSQL & CalculationsToAdd(x)
End Loop

strSQL = strSQL & " FROM tblCurrentdata INNER JOIN...."

Run strSQL

 

[JLCantara]

Here is a simple (well it's already created and tested) solution to your issue:

 Public Function Comparaison()
     Dim rstA As Recordset, rstB As Recordset, rstT As Recordset
    Dim str As String
    Dim I As Integer, J As Integer
    
    Set rstA = CurrentDb.OpenRecordset("CurrentData")
    Set rstB = CurrentDb.OpenRecordset("PreviousData")
    If rstA.RecordCount <> rstB.RecordCount Then
        GoTo ExitProc
    End If
    
    str = "DELETE * FROM ModifiedRecords"
    DoCmd.RunSQL str
    
    Set rstT = CurrentDb.OpenRecordset("ModifiedRecords")
    rstA.MoveLast
    rstA.MoveFirst
    rstB.MoveLast
    rstB.MoveFirst
    For I = 1 To rstA.RecordCount
        For J = 0 To rstA.Fields.Count - 1
            If rstA.Fields(J) <> rstB.Fields(J) Then
                rstT.AddNew
                    rstT.Fields(0) = rstA.Fields(0)
                rstT.Update
            End If
        Next J
        rstA.MoveNext
        rstB.MoveNext
    Next I
    
ExitProc:
    Set rstA = Nothing
    Set rstB = Nothing
    Set rstT = Nothing
    
End Function

Table ModifiedRecords has one field QuoteNum

Good luck, JLC.

 

[slimjen1]

Thanks all for your help. I'll try this when I can get back to my laptop

 

[slimjen1]

Here is a simple (well it's already created and tested) solution to your issue:

 Public Function Comparaison()
     Dim rstA As Recordset, rstB As Recordset, rstT As Recordset
    Dim str As String
    Dim I As Integer, J As Integer
    
    Set rstA = CurrentDb.OpenRecordset("CurrentData")
    Set rstB = CurrentDb.OpenRecordset("PreviousData")
    If rstA.RecordCount <> rstB.RecordCount Then
        GoTo ExitProc
    End If
    
    str = "DELETE * FROM ModifiedRecords"
    DoCmd.RunSQL str
    
    Set rstT = CurrentDb.OpenRecordset("ModifiedRecords")
    rstA.MoveLast
    rstA.MoveFirst
    rstB.MoveLast
    rstB.MoveFirst
    For I = 1 To rstA.RecordCount
        For J = 0 To rstA.Fields.Count - 1
            If rstA.Fields(J) <> rstB.Fields(J) Then
                rstT.AddNew
                    rstT.Fields(0) = rstA.Fields(0)
                rstT.Update
            End If
        Next J
        rstA.MoveNext
        rstB.MoveNext
    Next I
    
ExitProc:
    Set rstA = Nothing
    Set rstB = Nothing
    Set rstT = Nothing
    
End Function

Table ModifiedRecords has one field QuoteNum

Good luck, JLC.

JL; this code works to give me the number of records that don't equal; but I need to get a list of quotes/records that don't so we can see the fields that don't match. Code to modify to give me this pls? Thanks

 

[vbaInet]

An intermediary table for storing calculated values isn't necessary and it will fail if multiple users are running it at the same time (unless you insert per-user records).

The method proposed where you build the SQL will perform much better than looping through a recordset, especially if you have a huge table.

 

[slimjen1]

I tried that and couldn't get it to work. I declared sql as string and used the code and I couldn't get it to work.

 

[vbaInet]

What we gave you wasn't actual code, it was an explanation of how the code should be written, it needs to be implemented.

The code from JLC should give you an idea of how to implement the pseudo-code we wrote. But if however you want to continue with JLC's method, then perhaps when next he's around he might be able advise.

 

[JLCantara]

@vbaInet #9: that's YOUR opinion, but you omit the fact that my solution works without any mods. In his post he uses 'the user' not 'the users'.

@SlimJen1

Where and what is the error??? It works fine on my A2013. What are you using?
Here is a union query that you will use for a mismatch report:

SELECT
"Current" AS Source, CurrentData.*

FROM
ModifiedRecords
INNER JOIN CurrentData ON ModifiedRecords.ID = CurrentData.QuoteNum

UNION

SELECT
"Previous" AS Source, PreviousData.*

FROM
ModifiedRecords
INNER JOIN PreviousData ON ModifiedRecords.ID = PreviousData.QuoteNum

ORDER BY QuoteNum;

Once created, select it and use the report wizard to create your report - group on QuoteNum.

 

[slimjen1]

JLC: I had an autonumber field in the tbl ModifiedRecords. I will run again and post back. Thanks

 

[JLCantara]

Make sure that ModifiedRecords has ony one field QuoteNum as a PK. This to avoid duplicates if there is more than 1 mismatch in any given pair of record.

 

[slimjen1]

I was getting an error because I made the QuoteNum the primary key so it wouldn't give me the duplicate quotenum. But the tables may have the same quote number for two or more states. So I need to be able to identify the quote number once. If I remove the primary key; I get the quotenum multiple times even if its only two states. I need to fix this.

 

[JLCantara]

No, no keep the PK. I just forgot a tiny detail: to avoid duplicates add, before the .edit
On Error Resume Next
and after the .Update
On Error Go To Erreur (I am francophone...) a standard error treatment bloc.

 the tables may have the same quote number for two or more states.

So you have to make the records unique using a PK - autonumber

Now save this PK instead of QuoteNum and replace QuoteNum with the actual PK in the union query...

 

[MarkK]

IMO it should be mentioned, and maybe I messed it somewhere in this thread: All this footwork is required because of an essential design flaw at the table level; that there are two different places to store the same type of thing.

The currency of data should be established not by the table that contains it, but by the data itself.

 

[vbaInet]

@vbaInet #9: that's YOUR opinion, but you omit the fact that my solution works without any mods. In his post he uses 'the user' not 'the users'.

I'm not disputing that the method you proposed works, I know that it works. I'm talking about the fact that the methods of:

1. looping through a recordset (in any situation)
2. Using the AddNew method
... are much slower than running a query - this is common knowledge and one that I'm sure that you are aware of. We've run timed tests on different methods and in every case, looping through a recordset has proven to be much slower. And if it was my opinion I would have stated so.

The keyword used in my statement "if multiple users are running it at the same time" was "if". Meaning, slimjen1 is to decide whether it applies or not and make appropriate judgement.

MarkK makes a good point and in slimjen1's other threads it's been highlighted a few times. But I think that slimjen1's challenge is that the db was inherited and as a result there's a limit as to how much change that's allowed. It will require a complete overhaul of the system.

 

[slimjen1]

I appreciate all comments. VBAINET; Most of my databases are inherited and require immediate fix and not a lot of time for overhaul. You may be correct that this may run slower than a query. I was just opting for the quickest solution to save time. With that being said; This is the current code running and I have tested it.

Public Function Comparaison()
    Dim rstA As Recordset, rstB As Recordset, rstT As Recordset
    Dim str As String
    Dim I As Integer, J As Integer
    
    Set rstA = CurrentDb.OpenRecordset("CurrentData")
    Set rstB = CurrentDb.OpenRecordset("PreviousData")
    If rstA.RecordCount <> rstB.RecordCount Then
        GoTo ExitProc
    End If
    
    str = "DELETE * FROM tblModifiedRecords"
    DoCmd.RunSQL str
    
    Set rstT = CurrentDb.OpenRecordset("tblModifiedRecords")
    rstA.MoveLast
    rstA.MoveFirst
    rstB.MoveLast
    rstB.MoveFirst
    For I = 1 To rstA.RecordCount
        For J = 0 To rstA.Fields.Count - 1
            If rstA.Fields(J) <> rstB.Fields(J) Then
            On Error Resume Next
                rstT.AddNew
                    rstT.Fields(0) = rstA.Fields(0)
                rstT.Update
              On Error GoTo 0
            End If
        Next J
        rstA.MoveNext
        rstB.MoveNext
    Next I
    
ExitProc:
    Set rstA = Nothing
    Set rstB = Nothing
    Set rstT = Nothing
    
    MsgBox "Update Complete."
    
End Function

It runs but not given the expected results as the results have no differences in the records between the two tables. So I am not sure why.

 

[slimjen1]

All; I created a sample database that seems to work with code and design flaws. It has to be something with the data. I'll have to find whats wrong. Please can someone review and make suggestions for a redesign and maybe I would get some time to redesign this flawed database. smh.