Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

comparing tables

Discussion in 'Information Technology' started by Jenn, Nov 1, 2005.

  1. Jenn

    Jenn
    Expand Collapse
    Guest

    I need some help with how to set up a comparison type query. I have a
    database that has a table called Employees.

    The Table has the following Fields:
    Employee Name
    Employee Number
    Salary Band
    Occupation Title

    This information comes from our corporate system and a report is generated
    in either a text or excel file. Every quarter we run this report and I import
    it into Access.

    What I would like to do is be able to import this file into Access as a
    “new†Employee table (it can be brought in either as a txt file or excel
    file) and I do not care what it is called. Then I would like to run a query
    that shows me if there are any difference between the new employee table that
    was just imported and the old employee table that exists.

    Specifically I would like to know if there are any new employee records in
    this new table, any records that do not exist in the new table that exist in
    the old table, and finally if there were any changes to the current records
    (i.e., an employee salary band has changed). I would also like to know what
    these differences are. Is there a query that can do this?
    TIA!
     
  2. Loading...

    Similar Threads Forum Date
    Sanatan Sikhi Comparing The Khalsa Rehat: Damdami, Nihang, Nanaksar, AKJ and Singh Sabha Traditions Sikh Sikhi Sikhism Oct 2, 2009
    Comparing Saints with Scholars Gurmat Vichaar Mar 20, 2008
    India 11 Constables Get Pregnant at Training School Breaking News Apr 26, 2011
    Warm Up with Winter Vegetables (Vegetarian Casseroles) Cooking & Recipies Feb 7, 2011
    India Two constables arrested for beating, extortion Breaking News Jul 27, 2010

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    > Is there a query that can do this?

    Both of these tables must include at least one column that is never changed.
    Otherwise, there's no way to match the records in one table with the
    corresponding records in the other table. Both the old and new values of
    each field must be compared to each another. In the following example, the
    Employee Number is assumed to be the primary key that never changes.

    SELECT Employees.[Employee Number], NewEmps.[Employee Number],
    Employees.[Employee Name], NewEmps.[Employee Name],
    Employees.[Salary Band], NewEmps.[Salary Band],
    Employees.[Occupation Title], NewEmps.[Occupation Title]
    FROM NewEmps RIGHT JOIN Employees ON NewEmps.[Employee Number] =
    Employees.[Employee Number]
    WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
    (((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
    (((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
    (ISNULL(NewEmps.[Employee Name]) AND ISNULL(NewEmps.[Salary Band]) AND
    ISNULL(NewEmps.[Occupation Title]))
    UNION
    SELECT Employees.[Employee Number], NewEmps.[Employee Number],
    Employees.[Employee Name], NewEmps.[Employee Name],
    Employees.[Salary Band], NewEmps.[Salary Band],
    Employees.[Occupation Title], NewEmps.[Occupation Title]
    FROM NewEmps LEFT JOIN Employees ON NewEmps.[Employee Number] =
    Employees.[Employee Number]
    WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
    (((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
    (((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
    (ISNULL(Employees.[Employee Name]) AND ISNULL(Employees.[Salary Band]) AND
    ISNULL(Employees.[Occupation Title]));

    How to read the comparisons:

    If the Employees.[Employee Number] is NULL, then it's a new record in NewEmps.
    If the NewEmps.[Employee Number] is NULL, then the record only exists in
    Employees.
    If Employees.[Employee Number] equals NewEmps.[Employee Number], then
    something in that record has changed, so compare the columns, old with new to
    find out what the change is.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.

    (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    be forwarded to me.)
    - - -
    If my answer has helped you, please sign in and answer yes to the question
    "Did this post answer your question?" at the bottom of the message, which
    adds your question and the answers to the database of answers. Remember that
    questions answered the quickest are often from those who have a history of
    rewarding the contributors who have taken the time to answer questions
    correctly.


    "Jenn" wrote:

    > I need some help with how to set up a comparison type query. I have a
    > database that has a table called Employees.
    >
    > The Table has the following Fields:
    > Employee Name
    > Employee Number
    > Salary Band
    > Occupation Title
    >
    > This information comes from our corporate system and a report is generated
    > in either a text or excel file. Every quarter we run this report and I import
    > it into Access.
    >
    > What I would like to do is be able to import this file into Access as a
    > “new†Employee table (it can be brought in either as a txt file or excel
    > file) and I do not care what it is called. Then I would like to run a query
    > that shows me if there are any difference between the new employee table that
    > was just imported and the old employee table that exists.
    >
    > Specifically I would like to know if there are any new employee records in
    > this new table, any records that do not exist in the new table that exist in
    > the old table, and finally if there were any changes to the current records
    > (i.e., an employee salary band has changed). I would also like to know what
    > these differences are. Is there a query that can do this?
    > TIA!
    >
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    So, you are the first one.
    Good on you



    "'69 Camaro" wrote:

    > > Is there a query that can do this?

    >
    > Both of these tables must include at least one column that is never changed.
    > Otherwise, there's no way to match the records in one table with the
    > corresponding records in the other table. Both the old and new values of
    > each field must be compared to each another. In the following example, the
    > Employee Number is assumed to be the primary key that never changes.
    >
    > SELECT Employees.[Employee Number], NewEmps.[Employee Number],
    > Employees.[Employee Name], NewEmps.[Employee Name],
    > Employees.[Salary Band], NewEmps.[Salary Band],
    > Employees.[Occupation Title], NewEmps.[Occupation Title]
    > FROM NewEmps RIGHT JOIN Employees ON NewEmps.[Employee Number] =
    > Employees.[Employee Number]
    > WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
    > (((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
    > (((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
    > (ISNULL(NewEmps.[Employee Name]) AND ISNULL(NewEmps.[Salary Band]) AND
    > ISNULL(NewEmps.[Occupation Title]))
    > UNION
    > SELECT Employees.[Employee Number], NewEmps.[Employee Number],
    > Employees.[Employee Name], NewEmps.[Employee Name],
    > Employees.[Salary Band], NewEmps.[Salary Band],
    > Employees.[Occupation Title], NewEmps.[Occupation Title]
    > FROM NewEmps LEFT JOIN Employees ON NewEmps.[Employee Number] =
    > Employees.[Employee Number]
    > WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
    > (((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
    > (((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
    > (ISNULL(Employees.[Employee Name]) AND ISNULL(Employees.[Salary Band]) AND
    > ISNULL(Employees.[Occupation Title]));
    >
    > How to read the comparisons:
    >
    > If the Employees.[Employee Number] is NULL, then it's a new record in NewEmps.
    > If the NewEmps.[Employee Number] is NULL, then the record only exists in
    > Employees.
    > If Employees.[Employee Number] equals NewEmps.[Employee Number], then
    > something in that record has changed, so compare the columns, old with new to
    > find out what the change is.
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips.
    >
    > (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    > be forwarded to me.)
    > - - -
    > If my answer has helped you, please sign in and answer yes to the question
    > "Did this post answer your question?" at the bottom of the message, which
    > adds your question and the answers to the database of answers. Remember that
    > questions answered the quickest are often from those who have a history of
    > rewarding the contributors who have taken the time to answer questions
    > correctly.
    >
    >
    > "Jenn" wrote:
    >
    > > I need some help with how to set up a comparison type query. I have a
    > > database that has a table called Employees.
    > >
    > > The Table has the following Fields:
    > > Employee Name
    > > Employee Number
    > > Salary Band
    > > Occupation Title
    > >
    > > This information comes from our corporate system and a report is generated
    > > in either a text or excel file. Every quarter we run this report and I import
    > > it into Access.
    > >
    > > What I would like to do is be able to import this file into Access as a
    > > “new†Employee table (it can be brought in either as a txt file or excel
    > > file) and I do not care what it is called. Then I would like to run a query
    > > that shows me if there are any difference between the new employee table that
    > > was just imported and the old employee table that exists.
    > >
    > > Specifically I would like to know if there are any new employee records in
    > > this new table, any records that do not exist in the new table that exist in
    > > the old table, and finally if there were any changes to the current records
    > > (i.e., an employee salary band has changed). I would also like to know what
    > > these differences are. Is there a query that can do this?
    > > TIA!
    > >
     
  5. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    > So, you are the first one.

    Nah. Eighth. Out of 900 newsgroups for the English language, these seven
    people have already earned one silver badge each since the inception of the
    badge program in June of '04:

    David Bartosik (MVP), Publisher, 11/7/2004
    garfield-n-odie (MVP), Word, 12/3/2004
    Alejandro Mesa, SQL Server, 3/22/2005
    Jim Thomlinson, Excel, 5/25/2005
    Jocelyn Fiorello (MVP), Outlook, 7/22/2005
    Duke Carey, Excel, 8/29/2005
    CyberTaz, Word, 9/29/2005

    However, in the next month, we should have four people who have earned at
    least one silver badge in the Access newsgroups. And by the end of the
    year, you will probably have earned four silver badges and three bronze
    badges. :)

    As you can see, it's quite unprecedented. So, good on you!

    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips.


    "Ofer" <Ofer@discussions.microsoft.com> wrote in message
    news:08F253C8-CB0F-4317-AD35-B3A5259DA30C@microsoft.com...
    > So, you are the first one.
    > Good on you
    >
    >
    >
    > "'69 Camaro" wrote:
    >
    >> > Is there a query that can do this?

    >>
    >> Both of these tables must include at least one column that is never
    >> changed.
    >> Otherwise, there's no way to match the records in one table with the
    >> corresponding records in the other table. Both the old and new values of
    >> each field must be compared to each another. In the following example,
    >> the
    >> Employee Number is assumed to be the primary key that never changes.
    >>
    >> SELECT Employees.[Employee Number], NewEmps.[Employee Number],
    >> Employees.[Employee Name], NewEmps.[Employee Name],
    >> Employees.[Salary Band], NewEmps.[Salary Band],
    >> Employees.[Occupation Title], NewEmps.[Occupation Title]
    >> FROM NewEmps RIGHT JOIN Employees ON NewEmps.[Employee Number] =
    >> Employees.[Employee Number]
    >> WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
    >> (((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
    >> (((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
    >> (ISNULL(NewEmps.[Employee Name]) AND ISNULL(NewEmps.[Salary Band]) AND
    >> ISNULL(NewEmps.[Occupation Title]))
    >> UNION
    >> SELECT Employees.[Employee Number], NewEmps.[Employee Number],
    >> Employees.[Employee Name], NewEmps.[Employee Name],
    >> Employees.[Salary Band], NewEmps.[Salary Band],
    >> Employees.[Occupation Title], NewEmps.[Occupation Title]
    >> FROM NewEmps LEFT JOIN Employees ON NewEmps.[Employee Number] =
    >> Employees.[Employee Number]
    >> WHERE (((NewEmps.[Employee Name])<>Employees.[Employee Name])) OR
    >> (((NewEmps.[Salary Band])<>Employees.[Salary Band])) OR
    >> (((NewEmps.[Occupation Title])<>Employees.[Occupation Title])) OR
    >> (ISNULL(Employees.[Employee Name]) AND ISNULL(Employees.[Salary Band])
    >> AND
    >> ISNULL(Employees.[Occupation Title]));
    >>
    >> How to read the comparisons:
    >>
    >> If the Employees.[Employee Number] is NULL, then it's a new record in
    >> NewEmps.
    >> If the NewEmps.[Employee Number] is NULL, then the record only exists in
    >> Employees.
    >> If Employees.[Employee Number] equals NewEmps.[Employee Number], then
    >> something in that record has changed, so compare the columns, old with
    >> new to
    >> find out what the change is.
    >>
    >> HTH.
    >> Gunny
    >>
    >> See http://www.QBuilt.com for all your database needs.
    >> See http://www.Access.QBuilt.com for Microsoft Access tips.
    >>
    >> (Please remove ZERO_SPAM from my reply E-mail address so that a message
    >> will
    >> be forwarded to me.)
    >> - - -
    >> If my answer has helped you, please sign in and answer yes to the
    >> question
    >> "Did this post answer your question?" at the bottom of the message, which
    >> adds your question and the answers to the database of answers. Remember
    >> that
    >> questions answered the quickest are often from those who have a history
    >> of
    >> rewarding the contributors who have taken the time to answer questions
    >> correctly.
    >>
    >>
    >> "Jenn" wrote:
    >>
    >> > I need some help with how to set up a comparison type query. I have a
    >> > database that has a table called Employees.
    >> >
    >> > The Table has the following Fields:
    >> > Employee Name
    >> > Employee Number
    >> > Salary Band
    >> > Occupation Title
    >> >
    >> > This information comes from our corporate system and a report is
    >> > generated
    >> > in either a text or excel file. Every quarter we run this report and I
    >> > import
    >> > it into Access.
    >> >
    >> > What I would like to do is be able to import this file into Access as a
    >> > "new" Employee table (it can be brought in either as a txt file or
    >> > excel
    >> > file) and I do not care what it is called. Then I would like to run a
    >> > query
    >> > that shows me if there are any difference between the new employee
    >> > table that
    >> > was just imported and the old employee table that exists.
    >> >
    >> > Specifically I would like to know if there are any new employee
    >> > records in
    >> > this new table, any records that do not exist in the new table that
    >> > exist in
    >> > the old table, and finally if there were any changes to the current
    >> > records
    >> > (i.e., an employee salary band has changed). I would also like to
    >> > know what
    >> > these differences are. Is there a query that can do this?
    >> > TIA!
    >> >
     

Share This Page