Displaying the newest record based on date modified

[T8JGT]

Hi

I am currently trying to write a query which will display only the latest record for a project e.g.

Project 1 - 01/02/05 <--- Will filter out this record
Project 1 - 02/02/05 <--- Will only display this record

Here is the SQL so far:

SELECT qryProjectDetails.ProjectBusinessArea, qryProjectDetails.ProjectServiceDesigner, tblServiceDesigner.ServiceDesignerExtension, qryProjectDetails.ProjectPAR, qryProjectDetails.ProjectName, qryProjectDetails.ProjectManager, qryProjectDetails.ProjectLiveDate, qryProjectDetails.ProjectRAG, qryReporting.SerRepQualityGateNumebr, qryReporting.SerRepServType, qryReporting.SerRepProjProgress, qryReporting.SerRepDateModified
FROM (tblServiceDesigner INNER JOIN qryProjectDetails ON tblServiceDesigner.ServiceDesignerName = qryProjectDetails.ProjectServiceDesigner) INNER JOIN qryReporting ON qryProjectDetails.ProjectID = qryReporting.ProjectID;

 

[PHV]

something like this ?
WHERE qryReporting.SerRepDateModified=(SELECT Max(SerRepDateModified) FROM qryReporting WHERE ProjectID=qryProjectDetails.ProjectID)

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

 

[T8JGT]

Works a treat!

Many Thanks