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!
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!