Selecting latest date from joined table

[njitter]

Hi,

i have the following tables:

tblStandard_Information and tblReview_Information. These have been joined with a key.
I have created a query to display some fields - view this image:

http://img137.imageshack.us/img137/8995/tablenc9.png.

SQL view:

SELECT tblStandard_Information.Product_Name, 
             tblStandard_Information.APR_Priority, 
             tblStandard_Information.RPV_Priority, 
             tblStandard_Information.High_Priority, 
             tblStandard_Information.In_Frequent, 
             tblReview_Information.Type, 
             tblReview_Information.Period_Start_Date, 
             tblReview_Information.Period_End_Date
FROM tblStandard_Information 
INNER JOIN tblReview_Information ON tblStandard_Information.StandardID = tblReview_Information.ReviewID;

The output is shown in this image:

http://img211.imageshack.us/img211/2232/outputqd7.png

I only would like to show the line with the highest period end (the yellow ones) for each product name. Is it possible to create a query for this?




---
It's never too late to do the Right thing

 

[PHV]

Typed, untested:

SELECT S.Product_Name, 
       S.APR_Priority, 
       S.RPV_Priority, 
       S.High_Priority, 
       S.In_Frequent, 
       R.Type, 
       R.Period_Start_Date, 
       R.Period_End_Date
FROM (tblStandard_Information AS S
INNER JOIN tblReview_Information AS R ON S.StandardID = R.ReviewID)
INNER JOIN (
SELECT X.Product_Name, Max(Y.Period_End_Date) AS LastDate
FROM tblStandard_Information AS X
INNER JOIN tblReview_Information AS Y ON X.StandardID = Y.ReviewID
GROUP BY X.Product_Name
) AS L ON S.Product_Name = L.Product_Name
WHERE R.Period_End_Date = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[njitter]

Tested, works great!

tnx

---
Happy Happy Joy Joy