Selecting the most current record for an item that has multiple records in a query

[Pooks_35]

I have searched through the forums and unless I'm not performing the right searches, I'm not find the answer, so I apologize in advance if the answer is out there already! I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID? Thanks and Happy Halloween!

 

[Smart8]

You could try max(reportID)

 

[Pooks_35]

That doesn't work as I need to select the max ReportID number for each client that has multiple records in the query.

 

[JHB]

Show some sample data and which result you want!

 

[Pooks_35]

JHB,
The attached file is some samples of the tables and queries I have in place. I need to find a way to pull the max ReportID per each client who has a completed pre and/or post survey specific to that ReportID. There will only be one active ReportID per client at any given time. Not all clients have multiple ReportIDs, but those that do, the active ReportID will always be the max number of the ReportIDs for that client. Thanks for any help you can provide.

 

[JHB]

Is this what you're looking for, else explain what should or should not be there?

 

[Pooks_35]

Yes that is exactly what I need! Now how did you do that? Thanks!

 

[JHB]

Below is the SQL-String, then you can add the need fields.

SELECT tbl_Clients.ClientID, tbl_Clients.LastName, tbl_Clients.FirstName, Max(tbl_Reports.ReportID) AS MaxReportID, tbl_Surveys.SurveyID, tbl_Surveys.GrantYear, tbl_Surveys.DateSurveyEntered, tbl_Surveys.SRAP, tbl_Surveys.ReturnPriortoVisit, tbl_Surveys.TestType, tbl_Surveys.AAWClient
FROM (tbl_Clients INNER JOIN tbl_Reports ON tbl_Clients.ClientID = tbl_Reports.Client) INNER JOIN tbl_Surveys ON tbl_Reports.ReportID = tbl_Surveys.ReportID
GROUP BY tbl_Clients.ClientID, tbl_Clients.LastName, tbl_Clients.FirstName, tbl_Surveys.SurveyID, tbl_Surveys.GrantYear, tbl_Surveys.DateSurveyEntered, tbl_Surveys.SRAP, tbl_Surveys.ReturnPriortoVisit, tbl_Surveys.TestType, tbl_Surveys.AAWClient;

 

[Pooks_35]

Hi JHB,
OK, I'm going to admit I'm being clueless right now. Do I put the entire SQL-String in the query and do I put it in the criteria under the ReportID? I'm trying to figure out where to put the string to make it work!
Thanks!

 

[JHB]

Create a new query. In the query switch to SQL-View and paste in the string I showed then run the query, nothing else.
Come back if you can't get it to work.

 

[Pooks_35]

Thank you! It worked great. I appreciate all of your help on this!

 

[JHB]

You're welcome, luck with your project.