Max Date problem with one value?

[Delboy14]

I have a table of patient consultations, I would like to run a query which selects the last consultation for patients. I have tried max(date), but the problem is one of the patients has only had one consultation and they do not show on the query, Does anyone know how to get around this.
The tabel looks like this.
PatientIndex Date
1 10/3/2001
1 14/3/2001
1 25/4/2001
2 1/4/2001
3 3/3/2001
3 4/5/2001

thanks in advance

 

[tlbroadbent]

This should give you what you want.

Select PatientIndex, Max([Date]) As LastDate
From tbl
Group By PatientIndex Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Delboy14]

sorry but thats not done it

 

[WingandaPrayer]

Would this do it

SELECT tblConsultationDates.PatientIndex, Last(tblConsultationDates.DateofConsultation) AS LastOfDateofConsultation
FROM tblConsultationDates
GROUP BY tblConsultationDates.PatientIndex;



based on
PatientIndex DateofConsultation
1 10/03/01
2 01/04/01
3 03/03/01
1 14/03/01
1 25/04/01
3 04/05/01


query

PatientIndex LastOfDateofConsultation
1 25/04/01
2 01/04/01
3 04/05/01



HTH

David

 

[tlbroadbent]

What result do you get when you use the query I supplied? I'm curious because that is a very simple and basic query. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Delboy14]

I tried this and it seems to work now, thanks

SELECT c.PatientIndex, c.ConsultDate, c.BTSStep, c.CurrentPEF, c.BestPEF, c.SymptomsNight, c.SymptomsDay, c.SymptomsActivity, c.Technique, c.ForgetPreventer, c.RescueSteroid, c.Technique, c.OftenBlueInhaler, c.AEAdmittance
FROM Consults AS c INNER JOIN [SELECT PatientIndex, max(ConsultDate) AS MaxDate
FROM Consults GROUP BY PatientIndex]. AS m ON (c.PatientIndex=m.PatientIndex) AND (c.ConsultDate=m.MaxDate);

 

[tlbroadbent]

I see. You did not provide us with all of the information needed to solve the problem. You never indicated that you were joining to another table. Providing more details about your question or problem will facilitate and expedite getting an appropriate answer.

Regardless, I'm glad you found a solution. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin