Mission Impossible - Comparing Files

[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]

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.

 

[George]

lalexander,
I had a similar mission in Access at my last position. There is a product
out on the market called "Total Access Detective" by FMS (I believe...if not,
a quick search on the internet will get the correct distributor). It has a
cost of about $200, but it also has a return guarantee if it does not suit
your needs. I was able to use this product with relative ease showing which
records were added, deleted, and (if changed) detailing all changes. Good
luck

 

[Jamie Collins]

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

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.

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

Jamie.

--

 

[MUJERBELLA20042002]

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]

Thanks for showing me the way

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

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.

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

Jamie.

--

 

[Gary Walter]

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

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

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.

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

Jamie.

--

 

[Jamie Collins]

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.

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);

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.

--

 

[apapw2004]

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.

 

[yuyu]

ghfjhfgjgd