Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unmatched Query (Access2000)

    Field service reps have to maintain a car stock inventory. Currently, we are emailed an Excel document (by part number) of what the powers that be say we have in our vehicles. We then match the part part numbers of the items we have against the Excel spread sheet annotating what we have and what we don't have but are supposed to have. Long, slow, tedious process.

    My plan is to import that spread sheet into Access and then zap the bar code labels into another table, run an unmatched query which should display all items we are supposed to have but did not zap (which means we don't have them). So far, so good...except...I've run into a problem.

    The problem is the quantity field. My unmatched query is based on the part number field so if the part number is scanned then it is matched but if the official quantity is 2 but only 1 was scanned, then it does not show up as unmatched since the part number matched. Is it possible to create an unmatched query based on two different fields? I'm open for an education.

    Thanks.

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

    Re: Unmatched Query (Access2000)

    The Unmatched Query Wizard will only let you specify one pair of fields on which to match. But after the Wizard has finished, you can view the query in design view and specify a second pair:
    - Drag a line from the field in one table to the corresponding field in the other table.
    - Double click the join line.
    - Select the option to return all records from the table in wich you're looking for unmatched records.
    - Click OK.
    - The arrow in both join lines should point in the same direction. If not, double click the new join line again and select the reverse option.
    - Switch to datasheet view.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unmatched Query (Access2000)

    Thanks, Hans! Works great!

Posting Permissions

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