How to compare and calculate results of two queries

[mrjj]

I have a table where I have all needed parts for various products to be build. Then I have another table where is listed out parts already in stock) so kind of bookkeeping list.

How do I get out list of parts still needed to be bought ?

I have made two queries

- reference query
part name, quantity, product name, department

- part lookup query
part name, used quantity, product name, department


So what is needed is a way to lookup both queries, check if lines do match (product name, part name, department) then deduct used quantity from reference quantity.
If result is negative = something wrong or parts are going somewhere else
positive = amount to be ordered = missing amount



Access built in query find unmatched can be used to check if there is not matching lines but now when lines do match except quantites I don´t know how to proceed.

[ranman256]

Take the 2 queries and join them at the fields, BUT it needs to be an OUTER join.
Dbl-click the join line, select ALL RECORDS FROM (qReference query)
(or switch if needed, opt 2, or 3.)

This should show whats missing in your list.

[hansendl]

Also, FWIW, it sounds like you may have a data normalization issue if you're tracking parts information in two separate tables.