Query to extract records by date 2

[N2Life]

I have a table (tblCostHistory) in Access 2000 with records like this:

PartID MyDate Cost
1 4/3/2004 $11.52
1 5/1/2004 $11.56
1 6/1/2004 $11.60
2 3/2/2003 $25.15
3 8/2/2003 $37.19
3 1/9/2004 $37.22
3 7/8/2004 $37.15

For a given date, I want to extract only the price that was current at that date for each PartID. For example, for date 4/19/2004, I would get:

PartID MyDate Cost
1 4/3/2004 $11.52
2 3/2/2003 $25.15
3 1/9/2004 $37.22

Everything I've tried gives me multiple records for some PartIDs. Need help from a clear-thinking SQL specialist, please.

 

[r937]

need a correlated subquery

select PartID
     , MyDate
     , Cost
  from tblCostHistory as X
 where MyDate =
     ( select max(MyDate)
         from tblCostHistory
        where PartID = X.PartID
          and MyDate <= #4/19/2004# )

rudy

http://r937.com/

SQL Consulting

 

[PHV]

Something like this ?
SELECT A.PartID, A.MyDate, A.Cost
FROM tblCostHistory A INNER JOIN
(SELECT PartID, Max(MyDate) As MaxDate FROM tblCostHistory WHERE MyDate<=#4/19/2004# GROUP BY PartID) M
ON (A.PartID=M.PartID) AND A.MyDate=M.MaxDate
;

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

 

[N2Life]

My thanks to both r937 and PHV! I added an ORDER BY clause to fit my specific needs and it works beautifully.