Find earliest date by group

[foxtrot123]

I have a junction table that stores patient visits (a patient can have 1 or more visits, and a visit type can be associated with 1 or more patients). For example:

PtVisitID  PatientID  VisitType  VisitDate
1          1          Checkup    7/1/10
2          1          Checkup    3/3/10
3          1          Wellness   5/1/10
----------------------------------------
4          2          Checkup    9/15/12
5          2          Checkup    1/10/08
etc.

I want to select the earliest visit (using VisitDate) for each patient, for each VisitType. For the above data, the select query should return:

PtVisitID  PatientID  VisitType  VisitDate
2          1          Checkup    3/3/10
3          1          Wellness   5/1/10
5          2          Checkup    1/10/08

I tried to do a Group By query (Group By PatientID and VisitType), and ask for the Min([VisitDate]). But I couldn't find a way to show the PtVisitID associated with the visit that gets selected. (If I include PtVisitID, I have to make it part of the Group By or derive some statistic off it, like average, etc.)

Any suggestions?

 

[jdraw]

Try this

Select ptVisitId, PatientId, visittype,visitDate
from patientvisits
where visitdate in 
( select MinOfVisitDate from 
  (SELECT PatientVisits.PatientID, PatientVisits.VisitType, Min(PatientVisits.VisitDate) AS MinOfVisitDate
   FROM PatientVisits
   GROUP BY PatientVisits.PatientID, PatientVisits.VisitType
   )
);

 

[foxtrot123]

Strange - your query works with the sample data I provided, but it didn't work with the data I've attached (which includes just a few more records from a hypothetical PatientID 3). PatientID 3 has three checkup visits. Instead of selecting the earliest one, the query selects two of them. I can't figure out why.

 

[jdraw]

Here's another method.

You create a query to get the minimumVisitDate per Patient perVisitType.

I called it jFindMinDateOfVisitByPatientAndVisitType

SELECT PatientVisits.PatientID
, PatientVisits.VisitType
, Min(PatientVisits.VisitDate) AS MinOfVisitDate
FROM PatientVisits
GROUP BY PatientVisits.PatientID
, PatientVisits.VisitType;

Then use that query and the PatientVisits table to create another query (final results).

See the attached jpg for the Design view.

Here's the underlying SQL

SELECT PatientVisits.PtVisitID
, PatientVisits.PatientID
, PatientVisits.VisitType
, PatientVisits.VisitDate
FROM PatientVisits INNER JOIN jFindMinDateOfVisitByPatientAndVisitType ON
 (PatientVisits.PatientID
 = jFindMinDateOfVisitByPatientAndVisitType.PatientID) AND 
(PatientVisits.VisitType = jFindMinDateOfVisitByPatientAndVisitType.VisitType) AND 
(PatientVisits.VisitDate = jFindMinDateOfVisitByPatientAndVisitType.MinOfVisitDate);

The final result using your table

PtVisitID	PatientID	VisitType	VisitDate
2	1	Checkup	03/03/2010
3	1	Wellness	01/05/2010
5	2	Checkup	10/01/2008
6	3	Checkup	07/09/2001
9	3	Wellness	12/05/2004
10	3	Office	03/08/2000
12	3	Injury	02/08/2000

Good luck with your project.

 

[foxtrot123]

That's the workaround I had come up with. I was hoping though to handle it all in one query using the subquery idea you sauggested in your first reply. May not be possible, though. But thank you for the for the suggestions.

 

[jdraw]

Foxtrot,

I played with these for quite a while and i think I have it now. The issue previously was that there were 2 dates the same and Access kept both records since I was checking for VisitDate In (..... ).

Anyway in 1 query, I think this will work,

SELECT 
P.PTVISITID,
P.PATIENTID,
P.VISITTYPE,
P.visitDate
FROM  Patientvisits P
       INNER JOIN
     (
          SELECT  
            S.PatientId,
            S.VISITTYPE,
            MIN (S.VisitDate) AS Min_DATE
          FROM     PatientVisits as S  
          GROUP BY S.PatientId,S.VisitType
      ) as  XX
                   ON P.PatientID = XX.patientID and
                          P.Visittype = XX.visittype   and
                          P.VisitDate =XX.Min_date;

Good luck.

 

[foxtrot123]

Well done! That works. Very much appreciated. Thank you.