Comparing 2 tables, returning only what is different?

[MBresnan]

I have to upload changes to our employees to our AP system (every employee is a vendor, for expense reimbursement). Currently we get 1 file with the entire population from our payroll system, whether or not something has changed on a particular record. So if we hire a new person, going from 500 employees to 501 (and nobody else's data changed) i have to upload 501 rows to our AP system.

I have to load it into an access DB to format it. I'd like to set up a query to compare the new upload to the previous one. From there, i only want to bring in rows from the new upload, where there is a difference from the old upload table. i added a column to both denoting "new" or "old".

I created a union query, but i'm hitting a wall as to where to go from there. is there some sort of way i can do the opposite of DISTINCT and return both rows that are different, and not the ones that match? then i can use only rows with the NEW distinction.

Any thoughts?

 

[Golom]

I see two possibilities

1. A row is different because it is new.

2. A row is different because a field or fields have changed but a row with that primary key existed in the old import.

I would distinguish between the two based on a primary key which, by definition, cannot contain duplicates.

Select New.* 
From New LEFT JOIN Old ON New.PK = Old.PK
WHERE Old.PK IS NULL

That should give you all the records in the New Import that were not in the old one.

Select New.* 
From New INNER JOIN Old ON New.PK = Old.PK
WHERE (New.Fld1 & New.Fld2 & New.Fld3 & New.Fld4 & [blue]etc.[/blue]) <>
      (Old.Fld1 & Old.Fld2 & Old.Fld3 & Old.Fld4 & [blue]etc.[/blue])

will give you those records in "New" that have a changed value but did exist in "Old".

You can combine the results with a UNION query.

If there are a lot of fields then you may want to use VBA to build that second query in the form

Select New.* 
From New INNER JOIN Old ON New.PK = Old.PK
WHERE New.Fld1 <> Old.Fld1
   OR New.Fld2 <> Old.Fld2  
   OR New.Fld3 <> Old.Fld3
   [blue]etc.[/blue]