Mission Impossible - Comparing Files

  • Thread starter Thread starter lalexander
  • Start date Start date
L

lalexander

I've been given the project of comparing a current Excel Spreadsheet against
a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current File
* Identify any changes that occur on the either file

This could possibly involves creating something called a - Changed File.

I've used a program called ExelDiff, but it failed miserably , when the
information being compared wasn't in the same position.
So I have turned to Access ( because of the unique employee ID )

This project was supposed to go live today - 10/7/04 using ExcelDiff ( but
somehow I don't think this is going to happen )

Any help outling my immediate course of action would be very much
appreciated.

Could someone in the Access Community Please help.
 
lalexander said:
I've been given the project of comparing a current Excel Spreadsheet
against a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current
File
* Identify any changes that occur on the either
file

This could possibly involves creating something called a - Changed File.

I've used a program called ExelDiff, but it failed miserably , when the
information being compared wasn't in the same position.
So I have turned to Access ( because of the unique employee ID )

This project was supposed to go live today - 10/7/04 using ExcelDiff (
but somehow I don't think this is going to happen )

Any help outling my immediate course of action would be very much
appreciated.

Could someone in the Access Community Please help.
Click to expand...
 
lalexander said:
I've been given the project of comparing a current Excel Spreadsheet against
a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current File
* Identify any changes that occur on the either file
Click to expand...

Here's a basic 'template' for comparing changes to two worksheets
sheets with a key column and a data column common to both, Table1
being the older and Table2 being the more recent table.

New rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
RIGHT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyKeyCol IS NULL
;

Amended rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
INNER JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyDataCol<>T2.MyDataCol
;

Deleted rows:

SELECT
T1.MyKeyCol, T1.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
LEFT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T2.MyKeyCol IS NULL
;
Could someone in the Access Community Please help.
Click to expand...

I'm from the Excel world and the above are Jet queries. I don't do MS
Access <g>.

Jamie.

--
 
lalexander said:
I've been given the project of comparing a current Excel Spreadsheet against
a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current File
* Identify any changes that occur on the either file

This could possibly involves creating something called a - Changed File.

I've used a program called ExelDiff, but it failed miserably , when the
information being compared wasn't in the same position.
So I have turned to Access ( because of the unique employee ID )

This project was supposed to go live today - 10/7/04 using ExcelDiff ( but
somehow I don't think this is going to happen )

Any help outling my immediate course of action would be very much
appreciated.

Could someone in the Access Community Please help.
Click to expand...
 
Thanks for showing me the way







Jamie Collins said:
lalexander said:
I've been given the project of comparing a current Excel Spreadsheet
against
a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current File
* Identify any changes that occur on the either file
Click to expand...

Here's a basic 'template' for comparing changes to two worksheets
sheets with a key column and a data column common to both, Table1
being the older and Table2 being the more recent table.

New rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
RIGHT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyKeyCol IS NULL
;

Amended rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
INNER JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyDataCol<>T2.MyDataCol
;

Deleted rows:

SELECT
T1.MyKeyCol, T1.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
LEFT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T2.MyKeyCol IS NULL
;
Could someone in the Access Community Please help.
Click to expand...

I'm from the Excel world and the above are Jet queries. I don't do MS
Access <g>.

Jamie.

--
Click to expand...
 
Hi Jamie,

Excellent, tight and sweet.

One *trivial* point on your "Amended rows"
WHERE clause....it may not work as
intended if either MyDataCol is Null
and its counterpart is not Null.

Amended rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
INNER JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyDataCol<>T2.MyDataCol

Using what I once learned from Michel,
I might change its WHERE clause to:

WHERE
NZ(T1.MyDataCol<> T2.MyDataCol, -1)
AND NOT
(T1.MyDataCol IS NULL
AND
T2.MyDataCol IS NULL);

I know...trivial point, but may be important
in some cases.

Thanks,

Gary Walter

Jamie Collins said:
lalexander said:
I've been given the project of comparing a current Excel Spreadsheet against
a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current File
* Identify any changes that occur on the either file
Click to expand...

Here's a basic 'template' for comparing changes to two worksheets
sheets with a key column and a data column common to both, Table1
being the older and Table2 being the more recent table.

New rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
RIGHT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyKeyCol IS NULL
;

Amended rows:

SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
INNER JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyDataCol<>T2.MyDataCol
;

Deleted rows:

SELECT
T1.MyKeyCol, T1.MyDataCol
FROM
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table1$] T1
LEFT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Tempo\db.xls;].[Table2$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T2.MyKeyCol IS NULL
;
Could someone in the Access Community Please help.
Click to expand...

I'm from the Excel world and the above are Jet queries. I don't do MS
Access <g>.

Jamie.

--
Click to expand...
 
Gary Walter said:
One *trivial* point on your "Amended rows"
WHERE clause....it may not work as
intended if either MyDataCol is Null
and its counterpart is not Null.
Click to expand...

That's no trival point, that's a great catch.
I might change its WHERE clause to:

WHERE
NZ(T1.MyDataCol<> T2.MyDataCol, -1)
AND NOT
(T1.MyDataCol IS NULL
AND
T2.MyDataCol IS NULL);
Click to expand...

Avoiding proprietary features (NZ isn't even avilable to Jet!), I'd prefer:

WHERE
T1.MyDataCol<>T2.MyDataCol
OR (T1.MyDataCol IS NULL AND NOT T2.MyDataCol IS NULL)
OR (T2.MyDataCol IS NULL AND NOT T1.MyDataCol IS NULL)
;

Jamie.

--
 
lalexander said:
I've been given the project of comparing a current Excel Spreadsheet against
a historical Spreadsheet.

They both have the same Employee ID.( Or new ones added on the Current
File )

My task is ( should I chose to accept - Mission Impossible )

Is to determine the following:

* How many new records are added on the Current File
* Identify any changes that occur on the either file

This could possibly involves creating something called a - Changed File.

I've used a program called ExelDiff, but it failed miserably , when the
information being compared wasn't in the same position.
So I have turned to Access ( because of the unique employee ID )

This project was supposed to go live today - 10/7/04 using ExcelDiff ( but
somehow I don't think this is going to happen )

Any help outling my immediate course of action would be very much
appreciated.

Could someone in the Access Community Please help.
Click to expand...
 
Import the two spreadsheets into access.

To find the new records, run the "Find Unmatched Query Wizard"

To identify changes on records that exist in both tables, open a new
query, and add both tables to it. Link the Employee ID fields and drag
each field from one of the tables onto the design grid. At the bottom
of the design grid put

Not [OtherTable]![FieldName]

in the criteria row, where FieldName is the name of the matching field
from the other table.
Do this for each field, but putting the criteria on a different row
each time, so that the query will show all matches.
 
Back
Top