Compare 2 tables and only show the changes in result sheet

[Rachel Curran]

Please can anybody help me with the following:

I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information for employees however, they are from two different dates
so by comparing both sheets I should to be able to check which records
DO NOT match.

From the records that DO NOT match I need to locate the information
that has changed. But I only want to bring in the fields that have
changed, so for example 1:

Spreadsheet1 - ID No:12345 DOB 28/02/78 Grade A Department Finance
Hours 37
Spreadsheet2 - ID No:12345 DOB 28/02/78 Grade B Department HR Hours
37

Result of query = ID No: 12345 DOB (blank)Grade B Department HR Hours
(blank)

Example 2:

Spreadsheet1 - ID No:56789 DOB 28/09/82 Grade B Department HR Hours
37
Spreadsheet2 - ID No:56789 DOB 28/09/82 Grade BS Department HR Hours
25

Result of query = ID No: 56789 DOB (blank)Grade BS Department
(blank)Hours 25

Any help would be a great help, thankyou in advance - I only know
access at intermediate level, so I have attempted to compare these
tables using following formula:

iif(Table1.DOB = Table2.DOB, true, false

But all this does is bring me back all records and displays -1 in the
columns where ive used the formula above - think a union query might
help but have no idea how to do this.

Please help

Rachel

[James Fortune]

cu***********@hotmail.com (Rachel Curran) wrote in message news:<f1**************************@posting.google. com>...

Please can anybody help me with the following:

I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information for employees however, they are from two different dates
so by comparing both sheets I should to be able to check which records
DO NOT match.

From the records that DO NOT match I need to locate the information
that has changed. But I only want to bring in the fields that have
changed, so for example 1:

Spreadsheet1 - ID No:12345 DOB 28/02/78 Grade A Department Finance
Hours 37
Spreadsheet2 - ID No:12345 DOB 28/02/78 Grade B Department HR Hours
37

Result of query = ID No: 12345 DOB (blank)Grade B Department HR Hours
(blank)

Example 2:

Spreadsheet1 - ID No:56789 DOB 28/09/82 Grade B Department HR Hours
37
Spreadsheet2 - ID No:56789 DOB 28/09/82 Grade BS Department HR Hours
25

Result of query = ID No: 56789 DOB (blank)Grade BS Department
(blank)Hours 25

Any help would be a great help, thankyou in advance - I only know
access at intermediate level, so I have attempted to compare these
tables using following formula:

iif(Table1.DOB = Table2.DOB, true, false

But all this does is bring me back all records and displays -1 in the
columns where ive used the formula above - think a union query might
help but have no idea how to do this.

Please help

Rachel

Try the following query:

SELECT 'ID No: ' & CStr(Table1.ID) & ' DOB: ' &
IIf([Table1].[DOB]<>[Table2].[DOB], Table2.DOB,'(blank)') & 'Grade: '
& IIf([Table1].[Grade]<>[Table2].[Grade] , Table2.Grade,'(blank)') & '
Department: ' & IIf([Table1].[Department]<>[Table2].[Department],
Table2.Department,'(blank)') & ' Hours: ' &
IIf([Table1].[Hours]<>[Table2].[Hours], Table2.Hours,'(blank)') As
Output FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE
(((Table1.DOB)<> Table2].[DOB])) OR
(((Table1.Grade)<>[Table2].[Grade])) OR
(((Table1.Department)<>[Table2].[Department])) OR (((Table1.Hours) <>
[Table2].Hours));

James A. Fortune