Getting latest date and ID 1

[Apollo13a]

Hello, I'm trying to retrieve the latest price date for a particular material ID. My db has 3 tables.
tblMaterialDesc
DescriptionID(PK)
MaterialDesc

tblSellerName
tblSellerNameID
SellerName

tblPrice
PriceDate
PriceEach
MarkUp
SellingPrice
tblSellerNameID
tblMaterialID

I'm using combo boxes to place the material ID and Seller ID into table tblPrice. Works fine. Now I want to place the latest prices into a listbox but I'm having trouble with the query to get the prices.

I'm trying to make a query that will return the latest date for a particular material and the price each.
Here's my SQL so far. The 12 parameter is just for testing.

 SELECT Max(tblPrice.PriceDate) AS MaxOfPriceDate, tblPrice.tblSellerNameID, tblPrice.tblMaterialID
FROM tblPrice
GROUP BY tblPrice.tblSellerNameID, tblPrice.tblMaterialID
HAVING (((tblPrice.tblMaterialID)=12));

When I add the price each I get all the records. I tried using this query as a subquery for a new query and placing links between this query and the tblPrice but I still get all the records.
How can I pull out the latest date and price for a particular material ID? I suppose tblPrice could be called a transition table. It's just the way I set it, although I can change it if you think it's set up wrong.

Thanks Jim

 

[PHV]

You may try this:
SELECT A.tblMaterialID, A.tblSellerNameID, A.PriceDate, A.PriceEach
FROM tblPrice AS A INNER JOIN (
SELECT tblMaterialID, Max(PriceDate) AS LastDate
FROM tblPrice GROUP BY tblMaterialID
) AS M ON A.tblMaterialID = M.tblMaterialID AND A.PriceDate = M.LastDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Apollo13a]

Thanks PHV, although I'm trying to get all Sellers that have a MaterialId=12 and show the latest prices(based on date) for each.
Jim

 

[Apollo13a]

I think I got it using this SQL

SELECT qryGetLowestPrice.MaxOfPriceDate, qryGetLowestPrice.tblMaterialID, tblSellerName.SellerName, tblPrice.PriceEach
FROM (qryGetLowestPrice INNER JOIN tblSellerName ON qryGetLowestPrice.tblSellerNameID = tblSellerName.tblSellerNameID) INNER JOIN tblPrice ON (qryGetLowestPrice.MaxOfPriceDate = tblPrice.PriceDate) AND (qryGetLowestPrice.tblMaterialID = tblPrice.tblMaterialID);

Jim

 

[Apollo13a]

Well, I think I spoke to soon. I'm still trying to get the PriceEach of each product(tblMaterialID=21) from each supplier based on the latest date. Here's my current SQL

SELECT Max(tblPrice.PriceDate) AS MaxOfPriceDate, tblPrice.tblMaterialID, tblPrice.tblSellerNameID, tblPrice.PriceEach
FROM tblPrice
GROUP BY tblPrice.tblMaterialID, tblPrice.tblSellerNameID, tblPrice.PriceEach
HAVING (((tblPrice.tblMaterialID)=21));

I tried using some VBA as criteria(basiclly the latest date) but that only gave me 1(one) result.
Any ideas?
Thanks, Jim

 

[PHV]

The generlised way:
SELECT A.tblMaterialID, A.tblSellerNameID, A.PriceDate, A.PriceEach
FROM tblPrice AS A INNER JOIN (
SELECT tblMaterialID, tblSellerNameID, Max(PriceDate) AS LastDate
FROM tblPrice GROUP BY tblMaterialID, tblSellerNameID
) AS M ON A.tblMaterialID = M.tblMaterialID AND A.tblSellerNameID = M.tblSellerNameID AND A.PriceDate = M.LastDate

For a particular tblMaterialID:
SELECT A.tblMaterialID, A.tblSellerNameID, A.PriceDate, A.PriceEach
FROM tblPrice AS A INNER JOIN (
SELECT tblSellerNameID, Max(PriceDate) AS LastDate FROM tblPrice
WHERE tblMaterialID = 21 GROUP BY tblSellerNameID
) AS M ON A.tblSellerNameID = M.tblSellerNameID AND A.PriceDate = M.LastDate
WHERE A.tblMaterialID = 21

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Apollo13a]

Thanks PHV, that's exactly what I need. Although I have to open the query in order to get it to run. I would like to use this query as the record source for a listbox but I get an error. How do I set the record source of the listbox to this query and run it from my main form? Also, does this type of query have a name? I want to do some reading up and learn how to do this. I have the developer's handbook and few others as well but I didn't see anything similiar to this in them.
Thanks Jim G

 

[Apollo13a]

PHV, I used the SQL in VBA (on Form Open) to set the row source for my listbox.
Thanks again