Finding the changes between 2 tables

W

WBTKbeezy

I am trying to find a way to locate the differences between two tables.
Currently we have a system where we upload an excel file to another system
then users to that system make changes to the information based on their
needs. After 2 weeks we download the new file (with all the changes) into a
new Excel file. The problem is we do not have any way of knowing what
information has changed between the original file we uploaded and the new
file 2 weeks later. Is there any way access can pull out the records that
are different between the two files into a report?
 
B

Brendan Reynolds

Your table will need to include at least one field that is never changed
(otherwise there will be no way to match a record in one table with a
corresponding record in the other) and you will need to compare the old and
new values of each field that can be changed. Here's an example using the
Orders table from the Northwind sample database. To keep the example
reasonably short, I've included only a subset of the fields in the Orders
table. In this example, the field that is never changed is OrderID ...

SELECT NewOrders.*
FROM NewOrders INNER JOIN OldOrders ON NewOrders.OrderID = OldOrders.OrderID
WHERE (((NewOrders.CustomerID)<>[OldOrders].[CustomerID])) OR
(((NewOrders.EmployeeID)<>[OldOrders].[EmployeeID])) OR
(((NewOrders.OrderDate)<>[OldOrders].[OrderDate])) OR
(((NewOrders.RequiredDate)<>[OldOrders].[RequiredDate])) OR
(((NewOrders.ShippedDate)<>[OldOrders].[ShippedDate])) OR
(((NewOrders.ShipVia)<>[OldOrders].[ShipVia])) OR
(((NewOrders.Freight)<>[OldOrders].[Freight]));
 
Top