finding "updated" information between an active and history tables

[martinmike2]

Hello,

I have an active table that is updated monthly, and a history table
where I store the previous months data. What I need to do is run a
comparison of the two tables and only show those records that have a
change. I am at a loss as to how to accomplish this.

Any help would be greatly appreciated.

 

[Brendan Reynolds]

Hello,

I have an active table that is updated monthly, and a history table
where I store the previous months data. What I need to do is run a
comparison of the two tables and only show those records that have a
change. I am at a loss as to how to accomplish this.

Any help would be greatly appreciated.

Here's an example that compares two copies of the Orders table from the
Northwind sample database. To keep the example concise, I've compared just
three columns. You'll need to compare all columns that may have changed.

SELECT Orders1.*, Orders2.*
FROM Orders1 INNER JOIN Orders2 ON Orders1.[Order ID] = Orders2.[Order ID]
WHERE (((Orders1.[Employee ID])<>[Orders2].[Employee ID])) OR
(((Orders1.[Customer ID])<>[Orders2].[Customer ID])) OR (((Orders1.[Order
Date])<>[Orders2].[Order Date]));

As a general rule, in most circumstances, you'd probably be better of using
one table, and using a query to select the current month's data.

 

[Golfinray]

Under queries there is an unmatched query that will find all unmatched records.

 

[martinmike2]

Brendan, your solution is perfect, thanks