Re: Mission Impossible - Comparing Files

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 10/15/04 

Date: Fri, 15 Oct 2004 05:53:50 -0500

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" wrote:
> "lalexander" <l_alexander@verizon.net> wrote ...
>
> > I've been given the project of comparing a current Excel Spread*** against
> > a historical Spread***.
> >
> > 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.
>
> --

Loading