Unmatched Query (Access2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Thanks, Hans! Works great!

Reply | Quote