Query to show latest price

[Simon]

I have a stock/ ordering systesm

i have a tblProducts and then i have a tblProductPurchasePRice

tblProductPurchasePRice hold the price and the date i bought new
stock. This table might have a lot of differnt prices as prices
change on reguarley
IDNumber
ProductID
Price
Date

tblProducts
ProductID
ProductNume


I would a qry that will show ever product along with the the latest
price i purchased the last lot of stock for (Based on date)


Can any one point me in right direction

 

[Jeff Boyce]

Nothing in what you described seems to hold purchase date, lot size or other
purchase related info.

I think you need that in there too...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Paul Shapiro]

You can think of it as two queries. The first gets the latest pricing date
for each productID, and the second gets the product price on that date.
There would be issues if two prices for the same product have the same date,
so I will assume {productID, priceDate} is unique. If you have multiple
prices for the same product on the same day, the times must be different.

Select ProductID, Price
From tblProductPurchasePrice as P
Join (
Select productID, max(priceDate) as LatestPriceDate
From tblProductPurchasePrice
Group By productID
) as Q
On Q.productID=P.productID And Q.LatestPriceDate=P.priceDate