Query to show latest price

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

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.

"Simon" wrote in message
...
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

Create a Totals query based on tblProductPurchasePRice.
1. Start by creating a select query based on tblProductPurchasePRice.
Include ProductID, Price and Date. (BTW, you need to change Date to another
name. Date is a reserved word in Access.)
2. Click the Sigma icon (looks like a capital E) on the menu at the topof
the screen.
3. Under Date, change Group By to Max.

This query will now give you the current price for each product.

Steve



"Simon" wrote in message
...
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

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

"Simon" wrote in message
...
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)