Query to show latest price based on dates

[Simon]

I have a shop database that hold products and the prices we purchase
items for

tblProducts ( ProductID, ProductName)

tblStockPurchasePrice (ID, ProductID,Price,PurchaseDate)


I would like a create a query that will show ever product along with
the latest purchase price based on the PurchaseDate.

Could any one let me know how to do somthing like this

 

[Allen Browne]

See:
http://www.mvps.org/access/queries/qry0020.htm
Group by the product, get the most recent date, and then use the subquery to
get the price for that product on that date.

If subqueries are new, here's an intro:
http://allenbrowne.com/subquery-01.html

Alternatively, use this extended replacement for DLookup():
http://allenbrowne.com/ser-42.html
You can tell it to sort by "PurchaseDate DESC" so it returns the price for
the most recent date for the product. (This will be slower than the
subquery, but useful if you don't have too many products or you need to take
it to a report.

 

[John Spencer]

Here is the SQL statement you would need. If I haven't mistyped, you should
be able to open up a query in SQL view and paste or type in the following to
get the desired results.

SELECT TblProducts.ProductID
, tblProducts.ProductName
, tblStockPurchasePrice.Price
, tblStockPurchasePrice.PurchaseDate
FROM (TblProducts INNER JOIN tblStockPurchasePrice
ON tblProducts.ProductID = tblStockPurchasePrice.ProductID)
INNER JOIN
(SELECT ProductID, Max(PurchaseDate) as LastDate
FROM tblStockPurchasePrice
GROUP BY ProductID) as MaxDate
ON tblStockPurchasePrice.ProductID = MaxDate.ProductID
AND tblStockPurchasePrice.PurchaseDate = MaxDate.LastDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County