Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to write a query in which I can compare one table to another one. I want to compare table B to table A. If there is any data in table B that does not match table A, I would want it to spit ou t in the query. Is there anyway to do this?

    Thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Y,

    If the two tables are to be compared only on the Key field you can do the following:

    Set up your query with the two tables.
    Double Click on the join line in the query.
    Select the 2: option & click OK {see attached}
    In the query grid include the key field from both tables and any other info you want.
    In Criteria row of key from the table where you want to find missing data enter Is Null.
    This will give you a list of the keys from the master table where the key is missing from the other table.

    It gets more complicated if you have missing data in both tables.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, I think this is a better way of explaining it. I have to export data out of two systems. A production system and a test system (there are four test systems). I am comparing the data to ensure that the chart of accounts information in all systems match. I can join the tables on the General Ledger account number but what i need the query to spit out is anything that doesn't match. So for example gl account number 123456 may have a tax code of "*" in the production system and a tax code of "+" in test system 1. I would need the query to spit out gl account 123456 because the tax codes are not the same in both systems. I used the tax code as an example but there are 13 fields that need to be reviewed and if any of the 13 do not match I want the query to spit out the account number and the field that doesn't match. Honestly, it can spit out all of the data it doesn't have to be the one field that was incorrect. I hope i explained this correctly.

    Thanks,

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Y,

    Here's the template for a query that should solve the stated problem. However it does have two limitations:
    1. It will not find if an AccountNO is missing or added to the test database, you would need variations of the first solution for that comparison.
    2. For some reason the <> comparrison does not find instances where one of the compared fields is blank! I don't know why this is so maybe someone else can provide the reason and maybe a solution.

    And of course you will have to add a row to the criteria for each of your 13 fields to be compared.

    The only other solution I can think of would to write a VBA routine that would step through each of the tables and compare record by record. The nice thing about this approach is that you could also find missing or added Account numbers.

    I hope this helps.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •