I have a database table which holds 1

[Delboy14]

I have a database table which holds a patient ID, a date and a medication level. here is a section of it:

Patient ID Date Step
1 11/3/00 2
1 30/3/00 5
1 25/4/00 2
2 13/3/00 2
2 01/5/00 2

I have two queries I must write on this table,
Query 1: The number of patients whose medication (step) has changed. (In this example it would show patient 1

Query Two: How many patients have had another appointment within four weeks. (Again patient one in this example)

Does anyone know how I can do these queires, thanks in advance

 

[tlbroadbent]

I hope I've understood. There is little info on the tables to use for the results you want. I've made some guesses.

1st query: Patients who have had a Step change.

Select PatientID, Min(step) As MinStep, Max(step) As MaxStep
From PatientInfo
Group By PatientID
Having Max(step)<>Min(step)

2nd Query: Patients with appointment in next four weeks.

This one is tricky because there is no indicator of appointment frequency except the data itself. I determined to use the minimum time between prior appointments for each patient to determine the date for the next appointment. This is all guesswork. Hopefully, it will get you started.

Select
a.PatientID,
max(a.ApptDate) As LastAppt,
dateadd(&quot;d&quot;, Min(datediff(&quot;d&quot;,b.ApptDate,a.ApptDate)), max(a.ApptDate)) As NextAppt,
Min(datediff(&quot;d&quot;,b.ApptDate,a.ApptDate)) As ApptGap
From PatientInfo a Inner Join PatientInfo b
On a.PatientId=b.PatientID
Where a.ApptDate>b.ApptDate
Group By a.PatientID
Having dateadd(&quot;d&quot;, Min(datediff(&quot;d&quot;,b.ApptDate,a.ApptDate)), max(a.ApptDate))<dateadd(&quot;d&quot;,28,getdate())
Terry Broadbent
Please review faq183-874.

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

 

[Delboy14]

I am using MS Access 2000.

tlbroadbent Thanks for your help above, I have got query one working. I have included more info on query 2 below.

I have to test each patient, to see if they had a follow up appointment within 4 weeks, so for patient 1 I have to check if Date2-Date1 is greater than 1 month, and then if Date3-Date2 is greater than 1 month etc. for each patient. I want to display the patients who have not had a follow up appointment within 4 weeks. so if the date between appointments is > 4 weeks then that patient should be displayed. Do you know how to do this?

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

 

[tlbroadbent]

Try this.

SELECT p1.PatientID, p1.ApptDate AS LastAppt, p2.ApptDate AS PriorAppt, DateDiff(&quot;w&quot;,[p2].[apptdate],[p1].[apptdate]) AS Elapsed
FROM Patients AS p1 INNER JOIN Patients AS p2 ON p1.PatientID = p2.PatientID
WHERE (((DateDiff(&quot;w&quot;,[p2].[apptdate],[p1].[apptdate]))>4) AND ((p1.ApptDate)>[p2].[apptdate])); Terry Broadbent
Please review faq183-874.

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

 

[Delboy14]

Yes that works great except for patients who have three consultations. The formual compares prior date as consult one with last appointment as consult 3. This is wrong as the patient did have an appointment in between. IS there a way to change the prior Consult Date if there are more than two consults? thanks for your help

 

[tlbroadbent]

Try this one.

SELECT PatientID, Max(Appt2) AS LastAppt, Max(Appt1) AS PriorAppt
FROM [SELECT p1.PatientID, p1.ApptDate AS Appt2, p2.ApptDate AS Appt1, DateDiff(&quot;w&quot;,p2.apptdate,p1.apptdate) AS Elapsed
FROM Patients AS p1 INNER JOIN Patients AS p2 ON p1.PatientID=p2.PatientID
WHERE (((DateDiff(&quot;w&quot;,p2.apptdate,p1.apptdate))>4) And ((p1.ApptDate)>p2.apptdate))]. AS p3
GROUP BY PatientID; Terry Broadbent
Please review faq183-874.

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

 

[Delboy14]

brilliant thats it thanks