Results 1 to 4 of 4
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Finding non-matches (2003)

    I have an Access database with tblAllHandles, a list of about 1,300 good customer IDs.

    I have an Excel worksheet with a list of about 250 customerIDs, a few of which do not have matching entries in tblAllHandles.

    I need to identify these non-matching entries so I can fix them, but I don't know where to begin.

    I can do it in either Access or Excel. I know how to import the Excel worksheet into Access, and I know how to export the Access table into Excel. What I DON'T know (but it seems I should) is how to find the non-matches.

    Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding non-matches (2003)

    If you import or link the Excel table into your Access database, you can use the Find Unmatched Query Wizard.

    - Activate the Queries section of the database window.
    - Click 'New' in the toolbar at the top of the database window.
    - Select 'Find Unmatched Query Wizard' and click OK.
    - In the first step of the wizard, select the Excel table, then click Next.
    - In the second step, select tblAllHandles, then click Next.
    - In the third step, identify the fields on which you want to compare the tables (CustomerIID vs CustomerID). Click the <=> button, then click Next.
    - In the fourth step, select one or more fields you want to show in the list. Click >> if you want all fields. Then click Next.
    - In the last step, provide a name for the query, then click Finish.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Finding non-matches (2003)

    If you are doing it in Excel, assuming the 250 row list is in A1:A250 of sheet1 and the Access list is A1:A1500 of sheet2, then in B1 on sheet1 enter:
    <code>=COUNTIF(Sheet2!$A$1:$A$1500,A1)</code>
    and copy down to B250. Zero means no match.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Finding non-matches (2003)

    Thanks guys!

    I used the Excel method, which was easier in my case.

    I should have been aware of the Find Unmatched Query -- I've looked at it but never used it.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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