Efficient way to compare data in Two identically structured tables.

Hi all,

I have 2 tables say t_OLD and t_NEW. The new has
corrections for audit purposes.

They are identical in all respects (i.e. new is a copy of
old and then changes are made to t_new)

I would like a quick way to cycle through all the the fields
in each table and compare the values to see if there are
differences.

I would like to AVOID having to hardcode a comparison of
each and every fieldname pair -- perhaps a public TABLE
compare function that would generate a list of all records
and the differences between the values in each pair.

Any thoughts on how to approach this are most welcome.


If anyone has done something like this I would appreciate
hearing about your strategy.

re: Efficient way to compare data in Two identically structured tables.

Why not create a query with the IDs from both tables joined and then use
StrComp to compare strings that are presumed to be the same and subtractions
to compare numbers. Non Zeros in either case indicate a difference

Phil

"Maur" <pksto@earthlink.net> wrote in message
news:aKupb.3562$9M3.1830@newsread2.news.atl.earthl ink.net...[color=blue]
> Hi all,
>
> I have 2 tables say t_OLD and t_NEW. The new has
> corrections for audit purposes.
>
> They are identical in all respects (i.e. new is a copy of
> old and then changes are made to t_new)
>
> I would like a quick way to cycle through all the the fields
> in each table and compare the values to see if there are
> differences.
>
> I would like to AVOID having to hardcode a comparison of
> each and every fieldname pair -- perhaps a public TABLE
> compare function that would generate a list of all records
> and the differences between the values in each pair.
>
> Any thoughts on how to approach this are most welcome.
>
>
> If anyone has done something like this I would appreciate
> hearing about your strategy.
>
>[/color]

re: Efficient way to compare data in Two identically structured tables.

I think I discussed this a while back with Andi Plotsky. Not sure
what the subject of the discussion was, but if you search, you should
find it. In short:

You would need to join the two tables on a non-autonumber
field/fields.

Steps (if memory serves):
given tblA and tblB

1. Run "Find Unmatched" wizard on A,B.
2. Repeat with B,A.

these are the ones you definitely need to look at.

To find the inconsistent fields in the remaining records, you'd need
to do something like

- create a query that inner joins the two tables.
- loop through the records in the query
- loop through the fields collection of the querydef (so you might
want to put tblA fields first, and then the tblB fields). To do that,
you'd determine the "offset" of the second table's records. Say the
query consists of 5 fields from each table, so you'd be looking at
qdf.Fields(0) to qdf.Fields(9), so you could compare them...

really simply:
1. loop through recordset
2. loop through fields in single record
3. log non-matching values, ie. rs.Fields(n).Value <>
rs.Fields(n+OFFSET).Value

HTH,
Pieter

re: Efficient way to compare data in Two identically structured tables.

This will give you any record that had any change to it BUT not list
what the change is.

Example:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.d = Table1.d) AND (Table2.c =
Table1.c) AND (Table2.b = Table1.b) AND (Table2.a = Table1.a) AND
(Table2.ID = Table1.ID)
WHERE (((Table1.ID) Is Null));

You can just go through the "Find Unmatched Query Wizard". The only
problem with that is that you can only link on one field. Therefore,
go into design view and link on ALL of the other fields.


"Maur" <pksto@earthlink.net> wrote in message news:<aKupb.3562$9M3.1830@newsread2.news.atl.earth link.net>...[color=blue]
> Hi all,
>
> I have 2 tables say t_OLD and t_NEW. The new has
> corrections for audit purposes.
>
> They are identical in all respects (i.e. new is a copy of
> old and then changes are made to t_new)
>
> I would like a quick way to cycle through all the the fields
> in each table and compare the values to see if there are
> differences.
>
> I would like to AVOID having to hardcode a comparison of
> each and every fieldname pair -- perhaps a public TABLE
> compare function that would generate a list of all records
> and the differences between the values in each pair.
>
> Any thoughts on how to approach this are most welcome.
>
>
> If anyone has done something like this I would appreciate
> hearing about your strategy.[/color]

re: Efficient way to compare data in Two identically structured tables.

If you use ADO you can open both tables and then cycle through the
field names as provided via ADO to perform the comparison - that way
you would only need to open tabelOld cycle through each record and
then each field against the conterpart in tableNew

"Maur" <pksto@earthlink.net> wrote in message news:<aKupb.3562$9M3.1830@newsread2.news.atl.earth link.net>...[color=blue]
> Hi all,
>
> I have 2 tables say t_OLD and t_NEW. The new has
> corrections for audit purposes.
>
> They are identical in all respects (i.e. new is a copy of
> old and then changes are made to t_new)
>
> I would like a quick way to cycle through all the the fields
> in each table and compare the values to see if there are
> differences.
>
> I would like to AVOID having to hardcode a comparison of
> each and every fieldname pair -- perhaps a public TABLE
> compare function that would generate a list of all records
> and the differences between the values in each pair.
>
> Any thoughts on how to approach this are most welcome.
>
>
> If anyone has done something like this I would appreciate
> hearing about your strategy.[/color]