Compare Tables

[jojones]

I have two tables in an Access db which, after a lot of processing has been done, should be exactly the same.
By this I mean that there should be the same number of records in each table, and each record should exist in both tables with exactly the same values in each field.

The tables are approx 600,000 records each. I have tried a couple of different ways of doing this, but my db seems to crash with all of them. So far I have tried...

Method 1:
1. Run count on both to make sure they are the same.
2. Run SELECT * FROM tblA UNION SELECT * from tblB; and get count from this.
3. If count is the same then I presume tables are identical.
4. If not, run Unmatched query on both tables to get differences.
Problem with Method 1. Unmatched query can't cope.

Method 2:
1. Append data from both tables into a new table, making the Amount field from one table negative.
2. Run a query to groupby all fields and sum the Amount and show results where the Amount <> 0.
Problem with Method 2. VERY slow. Actually as we speak it has just completed,not sure how successfully.

Should I try doing it with recordsets, concatenating the entire record, one at a time, from one table and searching for that concatenated string throughout the other table (concatenating the records also, of course)?
If so, I can do most of it, but would like some help with the Find code.

If not, does anyone know a better way to do this.

thanks in advance

 

[AWithers]

I would say try running the unmatched again but add a criteria so you resticte the work required

e.g. amount>1000
then amount >2000
etc. etc.

Another way to do it would be to create a new table and put a primary key on the combination of fields which make a record unique (add an additional field to define which table the data came from)

then append all data from table a then append all data from table b. If b is the same as a then the second append will return errors for every record if not then it will add any unmmatched records. the only pone of concern will then be those added from table b

Hope this helps

Andy

 

[Tranman]

jojones,
Here is a quick & dirty function to compare your tables. You need to set intColCount to the number of columns in your tables, and of course, you need a reference to the DAO in your project. This takes about 1 min 30 sec to process two tables with 150,000 rows and 37 columns.

Good luck,
Paul

Public Function MatchRecs()
Dim intColCount As Integer
Dim intColPtr As Integer
Dim rs1 As Recordset
Dim rs2 As Recordset

intColCount = 37

Set rs1 = CurrentDb.OpenRecordset(&quot;SELECT * FROM CANSYS1_SEG ORDER BY SEQUENCE_NUMBER;&quot

Set rs2 = CurrentDb.OpenRecordset(&quot;SELECT * FROM CANSYS1_SEG_2 ORDER BY SEQUENCE_NUMBER;&quot

'SEQUENCE_NUMBER is the primary key

Do While Not rs1.EOF
For intColPtr = 0 To intColCount - 1
If rs1.Fields(intColPtr) <> rs2.Fields(intColPtr) Then
MsgBox &quot;Mismatch on rec &quot; & rs1.Fields(0) & &quot; Column &quot; & intColPtr, vbOKOnly, &quot;MISMATCH&quot;
End If
Next
rs1.MoveNext
rs2.MoveNext
Loop

rs1.Close
rs2.Close

MsgBox &quot;I'm done.&quot;, vbOKOnly, &quot;DONE&quot;

End Function

 

[jojones]

Tranman

I did try this, but my own variation. What I found was that if there was an additional record in one file, once the procedure got to that record the records would not match past that point as one recordset would be one record &quot;in front&quot; of the other.

Jo

 

[Tranman]

Jo,
Of course you're right. The function is kind of brain dead. No functionality to sync back up once you encounter an extra record. But, I thought that was what you were after....something to find *any* differences in two tables that were supposed to be exactly alike (including record count).

I guess you must have gotten about a zillion errors after encountering the extra record. What I might do is to put an exit function after the msgbox, then you could go in manually and fix the extra record (kludge in a temporary primary key that would cause it to be sorted to the end of the recordset). That way you could make it through the whole comparison.

Or, you could make some data change to the &quot;extra&quot; row (like making the first name be &quot;ZZZZ........&quot; or something like that, then at the bottom of your loop where you do the movenext's, just say something like&quot;

rs1.movenext
if left(rs1.fields(&quot;FNAME&quot,4) = &quot;ZZZZ&quot; then rs1.movenext
or
rs2.movenext
if left(rs2.fields(&quot;FNAME&quot,4) = &quot;ZZZZ&quot; then rs2.movenext
(whichever one is the longer recordset)

Or, you could put a breakpoint on the msgbox, and if you knew where the extra record was, just do a movenext on the appropriate recordset at that point, then continue on.

The more sophisticated comparison tools that attempt to resynchronize files of unequal lengths (Textpad comes to mind), do it by setting a bookmark, then reading forward in each file, looking for an exact match, and if they find one, resuming processing at that point. You could do the same thing with your recordsets, but I doubt if you want to go there.

All of the temporary solutions assume that you (or someone else like you who knows the data) will be running the comparison. If you want to do it in production, of course you will need to do it the hard way.

Let me know if you are going to resynch the recordsets and I will be glad to lend a hand.

Paul