curran_rachel@hotmail.com (Rachel Curran) wrote in message news:<f1e5f432.0406100348.4877efa4@posting.google. com>...[color=blue]
> 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[/color]
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
