Queries with Junction Tables

I have a query that takes 2 junction tables and uses a calculated field to come up with an on-hand count. Unfortunately, I'm getting multiple values for each product code when I simply want one count listed for each product ID. Here is what I'm using for my calculated field:

On Hand: [tbl Purchase Order Detail]![QtyReceived]-[tbl Sales Order Detail]![QtySold]

Because both of these tables are junction tables, I'm getting every instance of a product's activity instead of just the net result of the ins and outs. In this query, I am using 2 fields from my products table (productID and description) and there are direct relationships between all 3 tables.

I tried using unique values and records in the query properties but multiple records are still showing.

Where am I going wrong?

Bob Larson: If you still have my example from last week, I am referring to the Inventory On Hand query.

Thanks for the great help on the forums!

Query each table separately. Group By ProductID and Sum the quantities.
Then join these queries on ProductID and find the difference.

These queries can be combined into a subquery structure.

Thanks Galaxiom, that worked. So, in the end I have 2 queries and 1 subquery that are 3 separate objects. I can hide the first 2 and just display the 3rd which contains the result but is there a way to nest these into 1 query?

To make them into one query will be something like this air code:


The queries in brackets are the subqueries.

The LEFT OUTER JOIN is used to include all Items that were bought. An INNER JOIN would only show items both bought and sold.

The Nz is used to give a zero value where there is a Null in the sold field.

Great...a little confusing but I get the idea.

Thank you Galaxiom!