Re: Mission Impossible - Comparing Files

From: Jamie Collins (jamiecollins_at_xsmail.com)
Date: 10/08/04

  • Next message: Re_Artù: "Re: Setup of Xp Dev in WXP SP2" 
    Date: 8 Oct 2004 02:00:25 -0700

    "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. 

    --
  • Next message: Re_Artù: "Re: Setup of Xp Dev in WXP SP2"
    Loading