Query

[nl]

How can I show just the first TWO rows (dates) for each patient id?

Thank you.

 

[Roger Carlson]

Add a TOP predicate to your query:

SELECT TOP 2...<field list>...FROM...<tablename>...ORDER BY...<field>...DESC

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.doc" which illustrates how to do this. You can find
it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233

There is also an article included with the sample that explains the top
query.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[nl]

Example: I have 10 patients, most of them have over 20 visits. I want to
have of all those patients the first 2 visits for each patient.

 

[John Spencer (MVP)]

Use a correlated subquery. The query would look something like the following
Replace PatientVisits with the name of your table and the field names with the
relevant names of your fields.

SELECT PatientVisits.*
FROM [PatientVisits]
WHERE VisitDate in
(SELECT Top 2 VisitDates
FROM [PatientVisits] as TEMP
WHERE Temp.PatientID = [PatientVisits].PatientID
ORDER BY VisitDates DESC)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County