Hi,

I have two tables, Product and StockTake.

Product - ProductID, ProductCode, Description.
StockTake - StockTakeID, StockTakeDate, ProductID, Quantity.

I want a query that would return the last StockTakeDate and Quantity for each ProductID in the product table.

At the moment I am using SELECT TOP 1, which obviously only returns the top record from the StockTake table.



current select query:
Code:
SELECT TOP 1 tblProduct.ProductID, tblStockTake.StockTakeDate
FROM tblProduct INNER JOIN tblStockTake ON tblProduct.ProductID = tblStockTake.ProductID;
I want to be able to create a form and a report, from this query, which would give me stock take levels for every product in my database.

Any help would be appreciated.

Thanks

jonnyuk3