UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating Query That Returns Only The Last/max Date For Multiple Records, Access 2016    
 
   
jklinephd
post Sep 9 2019, 08:50 AM
Post#1



Posts: 78
Joined: 11-September 07
From: Missouri


I have a query running that is not giving the return I thought it would. It draws from a table that has a list of evaluations for each client that have been completed and the date it was completed. Some clients have more than 1 evaluation. I want the query to show the record for each client and only display their last evaluation (which is the latest date). I have tried using Totals and setting Date_Report_sent as Max and Last, and neither worked. Here is the SQL Code

CODE
SELECT Clients.temp_id, Clients.med_rec_num, Clients.Date_Report_sent, Clients.last_name, Clients.Findings, Clients.admit_date, Clients.type_eval, Clients.Next_due, Clients.Examiner, Clients.reason_admit_1, Clients.reason_admit_2, Clients.reason_admit_3, Clients.reason_admit_comm, Clients.reason_stay_1, Clients.reason_stay_2, Clients.reason_stay_3, Clients.reason_stay_comm, Clients.report_status
FROM Clients
WHERE (((Clients.admit_date)>#1/1/2016#) AND ((Clients.type_eval)="IST") AND ((Clients.report_status)="Completed"));


I thought Max would work, but it does not (I removed it in this SQL).

temp_id = is a unique id Number for every evaluation. med_rec_num is a unique number for each client.

There has to be an easy to do this?

Thanks

Jeff
Go to the top of the page
 
GroverParkGeorge
post Sep 9 2019, 09:01 AM
Post#2


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


"...and neither worked. "

Does that mean the query raises an error? Or that the wrong results are returned? Or too many results? Or results are omitted?

Thanks for clarifying the specific problem.
This post has been edited by GroverParkGeorge: Sep 9 2019, 09:02 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 9 2019, 09:24 AM
Post#3


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


Well, I'm going to proceed on an assumption, so if this doesn't address the right problem, let me know.

SQL
SELECT Clients.temp_id, Clients.med_rec_num, Clients.Date_Report_sent, Clients.last_name, Clients.Findings, Clients.admit_date, Clients.type_eval, Clients.Next_due, Clients.Examiner, Clients.reason_admit_1, Clients.reason_admit_2, Clients.reason_admit_3, Clients.reason_admit_comm, Clients.reason_stay_1, Clients.reason_stay_2, Clients.reason_stay_3, Clients.reason_stay_comm, Clients.report_status
FROM Clients INNER JOIN (SELECT med_rec_num , Max( Date_Report_Sent) AS Last_Report_Date FROM Clients as Temp GROUP BY med_rec_num ) AS LastEval ON Clients.med_rec_num = LastEval.med_rec_num AND Clients.Date_Report_Sent = LastEval.Last_Report_Date
WHERE (((Clients.admit_date)>#1/1/2016#) AND ((Clients.type_eval)="IST") AND ((Clients.report_status)="Completed"));


This involves a subquery to return the most recent "Date_Report_sent" for each client. That is joined back to the Clients table on the client's med_rec_num and Date_Report_Sent to limit the return to only the most recent one for each client.

Again, if that isn't the reason for the problem, please advise.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Sep 9 2019, 09:25 AM
Post#4



Posts: 1,484
Joined: 2-April 09
From: somewhere out there...


it will never work because of your criteria.
only for "1ST" eval is returning.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Sep 9 2019, 09:33 AM
Post#5


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


Good point, Arnel. I made a couple of assumptions, including the Max Date one. The other assumption is that the other criteria are all valid for the results required, i.e. that "IST" would be one type of evaluation that should be included, maybe "Infected Site Trauma" or something that would be abbreviated as "IST". I see that could also be interpreted as 1st or "first" and that implies a different kind of criteria as you note.

Again, clarification is needed from the poster.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jklinephd
post Sep 9 2019, 09:47 AM
Post#6



Posts: 78
Joined: 11-September 07
From: Missouri


QUOTE
Good point, Arnel. I made a couple of assumptions, including the Max Date one. The other assumption is that the other criteria are all valid for the results required, i.e. that "IST" would be one type of evaluation that should be included, maybe "Infected Site Trauma" or something that would be abbreviated as "IST". I see that could also be interpreted as 1st or "first" and that implies a different kind of criteria as you note.

Again, clarification is needed from the poster.


That query seems to work (scanned it and no duplicates, but its pulling 200+ records). To clarify. The original Query pulled every record for every client in the table. So what you gave me works at pulling the last evaluation from each client. BTW, IST is a type of evaluation and stands for Incompetent to Stand Trial. Now to work on using it with more queries to further limit the data.

This great help.

Thanks!!
Go to the top of the page
 
GroverParkGeorge
post Sep 9 2019, 10:23 AM
Post#7


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


Excellent. Glad Arnel and I could help.

Continued success with the project.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 9 2019, 05:51 PM
Post#8


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


I just realized I made an error. I failed to account for the possibility that a client's most recent result would NOT be "IST".

Corrected SQL to account for that:

SQL
SELECT Clients.temp_id, Clients.med_rec_num, Clients.Date_Report_sent, Clients.last_name, Clients.Findings, Clients.admit_date, Clients.type_eval, Clients.Next_due, Clients.Examiner, Clients.reason_admit_1, Clients.reason_admit_2, Clients.reason_admit_3, Clients.reason_admit_comm, Clients.reason_stay_1, Clients.reason_stay_2, Clients.reason_stay_3, Clients.reason_stay_comm, Clients.report_status
FROM Clients INNER JOIN (SELECT med_rec_num , type_eval, Max( Date_Report_Sent) AS Last_Report_Date FROM Clients AS Temp GROUP BY med_rec_num, type_eval) AS LastEval ON (Clients.med_rec_num = LastEval.med_rec_num) AND (Clients.Date_Report_Sent = LastEval.Last_Report_Date) AND (Clients.type_eval = LastEval.type_eval)
WHERE (((Clients.admit_date)>#1/1/2016#) AND ((Clients.type_eval)="IST") AND ((Clients.report_status)="Completed"));

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:23 PM