Comparing two tables (same structure) and show difference

[Stumped]

Hi,

Access 2003.

I need to compare two tables which of the same structure and show the
difference (data).

Bascially we have a Table (i.e. CurrentEmployeeTable) which the whole table
is copied as a backup (PreviousEmployeeTable in the same database) at the end
of each month so we have an audit trail. Now we need to compare the
CurrentEmployeeTable (which has had some modification (no deletions) during
the month) to the PreviousEmployeeTable.

Any help appreciated. Thanks
Stumped

 

[Jeff Boyce]

An alternative to having to maintain two tables with employee-related
information is to have a single table, and add a single field
(TerminationDate, or some such), in which you record the date the employee
left service.

Now all your queries, forms, reports can point at the single table, to find
anyone currently or previously an employee.

 

[Stumped]

Hi Jeff

Actually the 2 tables contains the same field. Basically i want to keep a
current eployee table, then the exact copy of it from a month ago. So that i
can keep an audit trail of the changes of the table from month to month.

What i need is to compare the 2 tables and show me if there are any
difference and what was changed ?

Alex

 

[Brendan Reynolds]

The first two SELECT clauses in the query below return records from each
table that don't exist in the other table. That's the easy part. The third
SELECT clause returns records that exist in both tables but have
differences. To keep this example short, I've compared only the FirstName
and LastName fields, but you may potentially have to compare many fields -
all fields that may change.

SELECT * FROM Employees WHERE EmployeeID NOT IN (SELECT EmployeeID FROM
Employees1)
UNION SELECT * FROM Employees1 WHERE EmployeeID NOT IN (SELECT EmployeeID
FROM Employees)
UNION SELECT Employees.* FROM Employees INNER JOIN Employees1 ON
Employees.EmployeeID = Employees1.EmployeeID WHERE Employees.FirstName <>
Employees1.FirstName OR Employees.LastName <> Employees1.LastName

 

[Jeff Boyce]

It sounds as if you are firmly wedded to the notion of two tables. But how
can you use just two to keep a month-to-month record of changes?

As mentioned before, a (potentially) simpler alternative is to use a single
table. If you need to keep an audit trail, check Allen Browne's website for
a way to do this.