Patient reattendance Query - How to find out which patients are reattending in your department

[Willniven]

Hi All
I have a problem with a query that I am trying to derive to see what patients are reattending in the emergency department
The query that I have covers 6 months of data 01/01/2010 to 30/06/2010. The following SQL definitely works when I join an attendances table onto itself again, but it continues to repeat the patient ID at a later stage if that patient reattends and then duplicates the reattendances . For example if one patient attended at 01/01/2010 14:09, 03/01/2010 18:07, 04/04/2010 12:09, 05/06/2010 14:10, then the query will return 03/01/2010 18:07, 04/04/2010 12:09, 05/06/2010 14:10 for the attendance that occured on 01/01/2010 but later on it will return 04/04/2010 12:09, 05/06/2010 14:10 for the attendance 03/01/2010 etc

SELECT [Attendances fin].Patient_ID, [Attendances fin].ARRIVAL_DATETIME, [Attendances fin_1].ARRIVAL_DATETIME
FROM [Attendances fin] INNER JOIN [Attendances fin] AS [Attendances fin_1] ON [Attendances fin].Patient_ID = [Attendances fin_1].Patient_ID
WHERE ((([Attendances fin].ARRIVAL_DATETIME)<#6/30/2010# And ([Attendances fin].ARRIVAL_DATETIME)<>[Attendances fin_1].[ARRIVAL_DATETIME]))
ORDER BY [Attendances fin].ARRIVAL_DATETIME, [Attendances fin_1].ARRIVAL_DATETIME;


What I want to do is isolate the patients that have repeat attendances and to count the number of patients that have reattended more than 2 times. Anyone got some bright ideas?!
Regards


Frustrated!

[orange]

General format: (but you can add fields and/or criteria)

SELECT PatientId, Count(Attendances) FROM YourTable
Group By PatientID
Having Count(Attendances) > 2;

However if re attended really means 3 attendances or more, then change the 2 to 3.

See this link to w3schools for details and sample

[Willniven]

Hi Orange
thank you for getting back to me so quickly! Your reply makes a lot of sense, but when I try to add in the Having clause with the count(attendances) greater than 2, it tells me that I have tried to execute a query that does not include the specified expression as part of an aggregate function. In my query I am also still getting the problem of repeated Patient ID numbers further down on the datasheet view when as I mentioned above if one patient attended at 01/01/2010 14:09, 03/01/2010 18:07, 04/04/2010 12:09, 05/06/2010 14:10, then the query will return 03/01/2010 18:07, 04/04/2010 12:09, 05/06/2010 14:10 for the attendance that occured on 01/01/2010 but later on it will return 04/04/2010 12:09, 05/06/2010 14:10 for the attendance 03/01/2010 etc.
Sorry to be such a pest, but I am so nearly there and its FRUSTRATING!
Thank you so much!

[orange]

Well I was giving a general answer based on your post. The details will be evident in your table --what fields exactly are in the table?
Please show us the SQL you used in your query.

[Willniven]

The fields in my query were
Patient ID, [Attendances fin].ARRIVAL_DATETIME and [Attendances fin_1].ARRIVAL_DATETIME I created an inner join of one attendance sheet onto the same sheet and then looked at where the repeat attendances were in the period of 01/01/2010 to 30/06/2010. So far so good but when it comes to isolating those patients that reattended, I have been a bit stuck


SELECT [Attendances fin].Patient_ID, [Attendances fin].ARRIVAL_DATETIME, [Attendances fin_1].ARRIVAL_DATETIME

FROM [Attendances fin] INNER JOIN [Attendances fin] AS [Attendances fin_1] ON [Attendances fin].Patient_ID = [Attendances fin_1].Patient_ID

WHERE ((([Attendances fin].ARRIVAL_DATETIME)<#6/30/2010# And ([Attendances fin].ARRIVAL_DATETIME)<>[Attendances fin_1].[ARRIVAL_DATETIME]))

ORDER BY [Attendances fin].ARRIVAL_DATETIME, [Attendances fin_1].ARRIVAL_DATETIME;

[orange]

Create a brand new query; and in SQL view; paste the following

SELECT Patient_Id, Count(Arrival_DateTime) FROM [Attendances Fin]
Group By Patient_ID
Having Count(Arrival_DateTime) > 2;

Then run the query

[Willniven]

This has been really helpful Orange
I am very grateful!
Kind Regards

[orange]

Happy to help.
Good luck with your project.