Queries with Junction Tables

danny westman

Registered User.
Local time
Today, 01:27
Joined
Feb 20, 2011
Messages
10
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:

Code:
SELECT A.Received-Nz(B.Sold) AS [On Hand]
FROM
    (SELECT Sum(QtyReceived) AS Received, ItemID
     FROM [tbl Purchase Order Detail]
     GROUP BY ItemID
     ) AS A
LEFT OUTER JOIN
    (SELECT Sum(QtySold)  AS Sold, ItemID
     FROM [tbl Sales Order Detail]
     GROUP BY ItemID
     ) AS B
ON A.ItemID = B.ItemID
ORDER BY ItemID

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.
 

Users who are viewing this thread

Back
Top Bottom