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:
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.

This is also posted on AccessForums.net, but i haven't had any help as of yet.

Thanks

jonnyuk3

Look at the dmax (http://www.techonthenet.com/access/functions/domain/dmax.php) function that should give you the top result

I would try a sub query.

See:

Get Information Associated with the Last Entry From a Table (http://www.baldyweb.com/LastValue.htm)

Hope this helps ...

Thanks HiTechCoach,

That works great.

jonnyuk3

You're welcome!

Glad that worked for you. :)