Comparing records

[Leslie67]

Is there a quick way to compare a record in two tables to determine if any changes have been made?

I have two tables with 16 fields. They *should* be identical. And I know for a fact that the first 3 fields of each table will never change.

What I want to do is compare the other 13 fields for any changes. Is there an easier way to do this other than by stringing a whole bunch of "OR"s together?

 

[JonFer]

If you just want to do a quick check, create a query in the query design window. If none of the fields have Nulls (unlikely) and there aren't any duplicate records WITHIN either table, you can join the tables on all of the fields. If the tables have the same values, the result should have the same number of rows as each table.

 

[MakeItSo]

Yeah, and if your RIGHT JOIN the fields, you should get all changed records. ;-)

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Leslie67]

Thanks Jonfer

and if I have lots of null fields?

I'm assuming that it won't work by joining the fields in that case, right?

 

[JonFer]

If you have a lot of Nulls, then you need to use "Is Null" and "Not Is Null" conditions to check if the same field is Null in both tables.

The Right/Left Join will tell you if the subordinate table has a different value because it will be Null after the join. However, if this field in both tables is Null, that isn't a change.

 

[MakeItSo]

Or you can use a "right" or "left" join.
right Click on the join in query designer and choose your desired option (all from second table and only those from first that match)

makeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos