Comparing Tables

[Jeff]

Is there anyway to compare tables and generate a report of all the
differences? We have sensitive data that gets input 2 times to ensure
accuracy of the data entry. Then each field (74 fields) gets compared
(manually) to verify accuracy. There has got to be a quicker way to
automate the double data entry and run a report against the two tables.
Currently the table structure of the 2 tables is identical.

Thanks,


Jeff

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CompareTwoTables.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff]

WOW, thanks for you help. That ROCKS!

Except it doesn't pickup discrepancies where one field is null and the other
is not null. Is there any way around that?

Thanks again,

 

[Roger Carlson]

Good question! I'd forgotten about Nulls completely. The problem comes in
when you try to compare a value with Null. You can't use the = or <> signed
when trying to compare fields that may have Nulls in them. It will ALWAYS
return false. So this line:
If rstBase(fld.Name) <> rstVarying(fld.Name) Then
Will return false even if rstBase(fld.Name) has a value but
rstVarying(fld.Name) is Null.

The solution is to use the NZ function around the fields that could contain
values. Like this:
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then

If you want to write the value of <Null> to your comparison table, you also
have to use the NZ function there too:
" has changed from " & Nz(rstBase(fld.Name), "<Null>") & _
" to " & Nz(rstVarying(fld.Name), "<Null>")

Similar changes have to be made to CompareTables2. I could list the changes
here, but I've modified the sample and put the new version up on my website.

Thanks for finding this glitch!

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L