Comparing Values in 2 Tables

[kopy]

I have a database. created by someone else, that uses a double entry scheme to ensure accurate data entry. I've been asked to compare the tables and return the records and fields that the values do not match. Alos, both tables have maxed out the number of fields.

Any suggestions of how I should approach this will be helpful.

Thanks,
Kopy

 

[JerryKlmns]

Join them on PK and check the records returned
Join them on PK and the 1st non-text field, then check the records returned
Join them on PK and the 2nd non-text field, then check the records returned
....
And so on.
For text fields If the duplicates where inserted automatically and not manually, then the previous trick should work for them, too.

Oh! Good luck

 

[lespaul]

both tables have maxed out the number of fields.

This makes me think that your tables are NOT normalized! What's the structure of this 256 field table? What kind of information are you storing?

(I understand you inherited this...I'm just being nosy!)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kopy]

Leslie,

The database is not normalized. Its not even relational. Inheritied work can be a real drag. If I had my way, I just build a new one. But, the clients do not want to pay for that considering they already paid for this. Ugh!

Kopy

 

[Golom]

The fact that they paid for shoddy goods shouldn't really be your problem. I would be tempted to charge something close to what it would cost to do it right just to sort this out.

Anyway ...

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim SQL As String
Dim fld As DAO.Field

SQL = "Select * From Table1 " 
Set rs1 = CurrentDb.OpenRecordset(SQL)

Do Until rs1.EOF
   SQL = "Select * From Table2 " & _
         "Where PrimaryKey = '" & rs1![PrimaryKey] & "'"
   Set rs2 = CurrentDb.OpenRecordset(SQL)
   For Each fld in rs1.Fields
      If fld.Value <> rs2.Fields(fld.Name).Value Then
         [COLOR=black cyan]' Found an unmatched field[/color]
         [COLOR=black cyan]' write it out [/color]
      End If
   Next
   rs1.MoveNext
Loop

I'm assuming that the two tables have identical fields.

 

[JerryKlmns]

Golom

You are also assuming that both tables have matching PKs! What if there is one, missing from Table1 and exists in Table2?

Er.. rs2.close before rs1.MoveNext ?

 

[Golom]

Jerry ... you're right ... good catch.

The obvious thing to do is flip it around and process table2 in the Do loop and look up values for table1. Of course, you would also need some additional code to test for EOF on the internal recordset (rs2 in the above).

 

[kopy]

First I'd like to thank Golom and JerryKlmns for their help. I've started using the code but I'm getting a compile error on the first line:

User-defined type not defined

 

[JerryKlmns]

Probably you need to add a reference to DAO

On menu Tools-->References click the Microsoft DAO 3.6 Object Library. Then On menu Debug --> Compile

 

[kopy]

So far everything has been helpful. The two tables are being cvompared and the fields with values that do not match are being selected. I veried this with a Debug.Print statement. I'd like to have the output be displayed on a form or report and do not know how to do this. Any help will be greatly appreciated. My current code is:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim SQL As String
Dim fld As DAO.Field

SQL = "Select * From [Diet 1 base and ang] "
Set rs1 = CurrentDb.OpenRecordset(SQL)

Do Until rs1.EOF
SQL = "Select * From [D1 base ang double entry] " & _
"Where [ptID] = '" & rs1![ptID] & "'"
Set rs2 = CurrentDb.OpenRecordset(SQL)
For Each fld In rs1.Fields
If fld.Value <> rs2.Fields(fld.Name).Value Then
' Found an unmatched field

' write it out
Debug.Print rs1![ptID]; rs1.Fields(fld.Name); rs2.Fields(fld.Name)

End If
Next
rs2.close
rs1.MoveNext

I'd like the output to have the PtID, Field Name (or Caption) Table1 Value, Table 2 value.

Thanks again, Kopy

 

[Remou]

You can append the data to a table or use a log file.

fname = CurrentProject.Path & "\errorlog.txt"
Open fname For Append As #1 'or output to overwrite
Do Until rs1.EOF
   SQL = "Select * From [D1 base ang double entry] " & _
         "Where [ptID] = '" & rs1![ptID] & "'"
   Set rs2 = CurrentDb.OpenRecordset(SQL)
   For Each fld In rs1.Fields
      If fld.Value <> rs2.Fields(fld.Name).Value Then
         ' Found an unmatched field
         
         ' write it out
         Print #1, rs1![ptID]; "   ";rs1.Fields(fld.Name); "   ";rs2.Fields(fld.Name)
                  
      End If
   Next
   rs2.close
   rs1.MoveNext
Loop
Close #1
FollowHyperlink fname

 

[kopy]

Thanks, this helps a lot. A couple of questions"

What is the code to cvhange this line to overwrite the current output? Open fname For Append As #1 'or output to overwrite

What is the code to output the caption of the field?

Thanks again, Kopy

 

[Remou]

1. Um, "'or output to overwrite"? Change Append to Output.
2. fld.Name Fields do not have captions, they have names.

 

[kopy]

Is there any way to get the caption for the field?

Also, when I use rs1.Fields(fld.Name)I get the value of the field. Shouldn't I be getting the field name?

 

[Remou]

From my post dated 19 Jan 07 8:29
2. fld.Name Fields do not have captions, they have names.

 

[kopy]

How would I send the out put to a table?

 

[Golom]

when I use rs1.Fields(fld.Name) I get the value of the field. Shouldn't I be getting the field name?

No. The default property is "Value". If you want the name then use

rs1.Fields(fld.Name)[red].Name[/red]

or more simply

fld.Name

since this code is designed to match the two fields by name.

 

[kopy]

I've finally got everything working. I's like to thank everyone for helping me. I've learned a lot.
You're a great group.

Thanks,
Kopy