Selecting the most current record for an item that has multiple records in a query (1 Viewer)

Pooks_35

Registered User.
Local time
Yesterday, 19:11
Joined
Mar 14, 2013
Messages
54
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!
 

Pooks_35

Registered User.
Local time
Yesterday, 19:11
Joined
Mar 14, 2013
Messages
54
That doesn't work as I need to select the max ReportID number for each client that has multiple records in the query.
 

JHB

Have been here a while
Local time
Today, 03:11
Joined
Jun 17, 2012
Messages
7,732
Show some sample data and which result you want!
 

Pooks_35

Registered User.
Local time
Yesterday, 19:11
Joined
Mar 14, 2013
Messages
54
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.
 

Attachments

  • Sample.zip
    363.1 KB · Views: 62

JHB

Have been here a while
Local time
Today, 03:11
Joined
Jun 17, 2012
Messages
7,732
Is this what you're looking for, else explain what should or should not be there?
 

Attachments

  • sur.jpg
    sur.jpg
    45 KB · Views: 125

Pooks_35

Registered User.
Local time
Yesterday, 19:11
Joined
Mar 14, 2013
Messages
54
Yes that is exactly what I need! Now how did you do that? Thanks!
 

JHB

Have been here a while
Local time
Today, 03:11
Joined
Jun 17, 2012
Messages
7,732
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

Registered User.
Local time
Yesterday, 19:11
Joined
Mar 14, 2013
Messages
54
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

Have been here a while
Local time
Today, 03:11
Joined
Jun 17, 2012
Messages
7,732
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

Registered User.
Local time
Yesterday, 19:11
Joined
Mar 14, 2013
Messages
54
Thank you! It worked great. I appreciate all of your help on this!
 

JHB

Have been here a while
Local time
Today, 03:11
Joined
Jun 17, 2012
Messages
7,732
You're welcome, luck with your project.
 

Users who are viewing this thread

Top Bottom