Query problem on selecting dates from a set 2

[Delboy14]

(Access 2002)
I am having a problem writing a query, I have a list of patient consultations and would like to select the first consultation of patients, but only those patients who have had more than one consultation.

SO select first Consultation of patients who have had more than one consultation. (I would also like to do the same for the Last Consultation in another table).

Up to now I have been able to create queries, which select the first consultation of patients or those who have had more than one consultation but I cannot do them both in the one query.

Thanks in advance, here is the table below.

PatientIndex ConsultDate
1 10/10/2000
1 16/11/2000
1 07/12/2000
2 10/11/2000
3 13/11/2000
3 29/11/2000
4 17/11/2000
4 14/12/2000
5 22/11/2000
5 13/12/2000
6 22/11/2000
6 14/12/2000
7 22/11/2000
7 13/12/2000
8 04/12/2000
8 16/12/2000

 

[JoeMiller]

I made a table called tblVisits and placed your data, here's what I came up with. Paste this into a new query in SQL View:

SELECT tblVisits.PatientIndex, Min(tblVisits.ConsultDate) AS FirstVisit, Count(tblVisits.ConsultDate) AS TotVisits
FROM tblVisits
GROUP BY tblVisits.PatientIndex
HAVING (((Count(tblVisits.ConsultDate))>1));

HTH
Joe Miller
joe.miller@flotech.net

 

[Delboy14]

thanks, thats it!!