Matching previous and next row

[watembo]

I have two columns: date of visit and date of next appointment. I would like a query that match the two to see if patients are coming on their appointment date. eg

patientID visitdate nextapp
1 20/3/12 29/4/12
1 28/4/12 30/5/12
1 30/5/12 30/6/12

In the above example the patient honoured the 2nd appointment (3rd visit). How do i pick out such?

all help much appreciated.

 

[G37Sam]

What I would do is, add a Yes/No field to the table named: "Honored", and upon insertion of the date of a new appointment, I would check using to see if it matches any previous NextApp. Here's how to do it using DAO:

Private Sub AppDate_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from Appointments where PatientID =" & _
    Me.PatientID & " AND NextApp =#" & Me.AppDate & "#")
If rs.EOF = False Then
    rs.Edit
    rs!Honored = True
    rs.Update
Else
    msgbox "No previous appointments honored"
End If
rs.Close
End Sub

 

[bob fitz]

Try this as the SQL of a query:

SELECT [B][COLOR=red]Table1[/COLOR][/B].patientID, [B][COLOR=#ff0000]Table1[/COLOR][/B].visitdate, [B][COLOR=#ff0000]Table1[/COLOR][/B].nextapp, IIf(DCount("nextapp","[B][COLOR=#ff0000]Table1[/COLOR][/B]","visitdate = #" & [nextapp] & "# And [patientID]=" & [patientID])>0,"Yes","No") AS [Appointment Kept]
FROM [B][COLOR=#ff0000]Table1[/COLOR][/B];

You would need to substitute Table1 with your table name.

 

[vbaInet]

patientID visitdate nextapp
1 20/3/12 29/4/12
1 28/4/12 30/5/12
1 30/5/12 30/6/12

In the above example the patient honoured the 2nd appointment (3rd visit). How do i pick out such?

Please elaborate on this statement. How is it a 2nd appointment a 3rd visit?

bobfitz has definitely given you a good start. You might also want to consider adding an extra condition to exclude a patient's first record as part of the check because there will be nothing to benchmark against.

 

[watembo]

Thanks alot good people. For G37sam my thought is that it would mean redesigning the form to check if the appointment was honored. bob fitz SQL returned an error
Invalid bracketing of name '[tblpatient_information.patient_ID]' and most were no's . The full SQL is like this:

SELECT tblpatient_information.patient_id, tblvisit_information.visit_date, tblvisit_information.next_visit_date, IIf(DCount("next_visit_date","tblvisit_information","visitdate = #" & [next_visit_date] & "# And [tblpatient_information.patient_ID]=" & [tblvisit_information.patient_ID])>0,"Yes","No") AS [Appointment Kept]
FROM tblpatient_information RIGHT JOIN tblvisit_information ON tblpatient_information.patient_id = tblvisit_information.patient_id
WHERE (((tblpatient_information.patient_id)=25));

with results as follows

Query1[FONT=&quot][/FONT]​

patient_id visit_date next_visit_date Appointment Kept[FONT=&quot][/FONT]
25 09/06/2011 01/09/2011 No
25 01/09/2011 24/11/2011 No
25 24/11/2011 15/02/2012 No
25 15/02/2012 16/05/2012 No
25 16/05/2012 15/08/2012 No


To answer vbaInet, the example I gave is a patient with 3 visits. On the first visit he was to come back on 29th april instead he came earlier on 28th. Then was give 30th may for which he honored. You are right, the first visit will always be assumed to be honored.

Hope this helps

 

[watembo]

Sorry guys. I corrected my SQL to a simpler

SELECT tblvisit_information.patient_id, tblvisit_information.visit_date, tblvisit_information.next_visit_date, IIf(DCount("next_visit_date","tblvisit_information","visit_date = #" & [next_visit_date] & "# And [patient_ID]=" & [patient_ID])>0,"Yes","No") AS [Appointment Kept]
FROM tblvisit_information WHERE (((tblvisit_information.patient_id)=25));

and now I eliminated the error. My problem is that all are still no, yet this patient has honored all the appointments. I do program in STATA and the equivalent of what I want would be found in using "_n-1" (previous record) or "_n+1" (next record) or "_n" (current record)

 

[vbaInet]

It's the other way round:

IIf(DCount("[COLOR=Red]*[/COLOR]", "tblvisit_information", "[COLOR=Red]next_visit_date[/COLOR] = #"  & [[COLOR=Red]visit_date[/COLOR]] & "# And [patient_ID]=" &  [patient_ID])>0,"Yes","No")

Once you have that working, you could look into converting it to an inline subquery.

 

[watembo]

The subquery worked absolute magic!

After this,
SELECT tblvisit_information.patient_id, tblvisit_information.visit_date, tblvisit_information.next_visit_date, (SELECT TOP 1 Dupe.next_visit_date
FROM tblvisit_information AS Dupe
WHERE Dupe.patient_id=tblvisit_information.patient_id
AND Dupe.visit_date<tblvisit_information.visit_date
ORDER BY Dupe.visit_date DESC, Dupe.patient_id) AS PriorValue
FROM tblvisit_information
WHERE (((tblvisit_information.patient_id)=36));

I got this...

patient_id visit_date next_visit_date PriorValue
36 31/05/2011 22/06/2011
36 22/06/2011 24/08/2011 22/06/11
36 24/08/2011 19/10/2011 24/08/11
36 12/10/2011 07/12/2011 19/10/11
36 01/12/2011 25/01/2012 07/12/11
36 25/01/2012 21/03/2012 25/01/12
36 21/03/2012 16/05/2012 21/03/12

The only BUT is that only one record is allowed at a time. I know that is easy for you...please help!

 

[vbaInet]

Based on what criteria?

 

[watembo]

I just want to return all records in the database. Presently I have filtered for only one record.

 

[vbaInet]

I don't understand. Give us an example of what you're trying to do like you've always done.

 

[watembo]

Thanks for your patience. My example is in post number 8. If I remove the WHERE option what I get is the message "At most one record can be returned by this subquery". I understand subquery works with only one record at a time. I have almost 3000 records which I want to match date of visit with date of appointment. Though there is the option of ANY I just don't know where to place it. I saw somewhere you had tried to tackle this before but frankly it seems to be outside my programming skills.

Hope this helps.

 

[vbaInet]

Yes apparently the TOP keyword doesn't always return 1 record and this causes a problem when used in subqueries. The Min and Max functions would be an alternative.