I am trying to construct a query that will look up the total number of units of a particular product that has been sold.

I have two tables...


I have one form "product2"which is a combination of these two tables.

Based on the current "ProductRef" value ("ProductRef" being the primary key field in the "PRODUCT" table), I need the query to look up all of the transactions with the same "ProductRef" value as is being displayed currently in the form, and Sum all of transactions by the "NumberofUnits" field.

Before you ask, I have created a "ProductRef" field in the "transactions" table and created the proper relationship.

So far I have this in the row source on the properties window for my text box field which is to display this total...

SELECT Sum([Transactions].[NumberofUnits]) AS SumOfNumberofUnits, [PRODUCT].[ProductRef] FROM PRODUCT INNER JOIN Transactions ON [PRODUCT].[ProductRef]=[Transactions].[ProductRef] GROUP BY [PRODUCT].[ProductRef] HAVING (((PRODUCT.ProductRef)=[Forms]![product2]![ProductRef]));

In SQL view it says...

SELECT Sum(Transactions.NumberofUnits) AS SumOfNumberofUnits, PRODUCT.ProductRef
FROM PRODUCT INNER JOIN Transactions ON PRODUCT.ProductRef = Transactions.ProductRef
HAVING (((PRODUCT.ProductRef)=[Forms]![product2]![ProductRef]));

However, this does not work properly, as although it correctly looks up and Sums all of the "NumberofUnits" for that particular product reference, it only does it for the first "ProductRef" value in my tables ("ProductRef"=1). When I scroll through the other products it just displays the same value. Even when I re-query it by hitting Shift>F9, the form just goes back to the record for "ProductRef" 1.

Any ideas?