Reseting the Waiting List clock

Wait start date is the waiting start date, ref date refers to the referral number of the patient. A patient can have more than one contact in a month.
Wait start date refers to the patient refered by ref no and should be reset if the status is 715 or 716.
This is what the status codes mean.
711 (Scheduled) - a meeting is scheduled in the near future
712(In Progress) - the patient is being seen
713(Attended and Seen) - patient is seen, so the waiting start date is set to the contact date and should come out of the loop
714(Attended and not seen)- the patient attended but the consultant has not seen
715(DNA) - patient did not attend
716 (Cancelled by Patient) –patient cancelled the appointment
717 (Cancelled by Provider) - the consultant cancelled the appointment.

pubic function update_wait
dim rs as dao.recodset
dim Pwtstdt as date
set rs = currentdb.openrecordse("select ...
do
 select case rs.fields("status")
         case '711','712','714','717'
               if pwtstdt = #1/1/1900# then pwtstdt = rs.fields("referrl_dte")
               rs.fields("waitstartdate") = pwtstdt
         case '713','715','716'
              rs.fields("waitstartdate") = rs.fields("contactdate")
end
rs.update
pwtstdt = rs.fields("waitstartdate")
if rs.eof then exit do
rs.movenext
loop

end function

HTH
Jag5x5

I get an error 'Run time error '3020': Update or Cancel Update without cancel without AddNew or Edit.
Also I have mentioned that once the status is 713, it has to come out of the loop and start looking at other referral numbers. Not sure whether you have given provision for that.
This is the code I modified::::


Public Function update_wait()
Dim rs As DAO.Recordset
Dim Pwtstdt As Date
Set rs = CurrentDb.openrecordset("select * from waiting_table")

Do
 Select Case rs.Fields("status")
        Case "711", "712", "714", "717"
               If Pwtstdt = #1/1/1900# Then Pwtstdt = rs.Fields("referral_dte")
               rs.Fields("waitstartdate") = Pwtstdt
         Case "713", "715", "716"
              rs.Fields("waitstartdate") = rs.Fields("contact_date")
         
End Select

rs.Update
Pwtstdt = rs.Fields("waitstartdate")
If rs.EOF Then Exit Do
rs.MoveNext
Loop

End Function

Sorry you just need to remove the 713 from the second case and add a new case for that and ad an rs.edit to the other cases

Public Function update_wait()
Dim rs As DAO.Recordset
Dim Pwtstdt As Date
Set rs = CurrentDb.openrecordset("select * from waiting_table")

Do
 Select Case rs.Fields("status")
        Case "711", "712", "714", "717"
               If Pwtstdt = #1/1/1900# Then Pwtstdt = rs.Fields("referral_dte")
                rs.edit
                rs.Fields("waitstartdate") = Pwtstdt
         Case  "715", "716"
              rs.edit
              rs.Fields("waitstartdate") = rs.Fields("contact_date")
         Case "713
            'do someother stuff
         
End Select

rs.Update
Pwtstdt = rs.Fields("waitstartdate")
If rs.EOF Then Exit Do
rs.MoveNext
Loop

End Function

HTH
Jag5x5

I ran the code...but there is one problem...the date of the previous contact should taken only if its got the same referral number. So I think you need to insert one more loop to go through the similar referral numbers.. Each ref number is a patient. and as such the previous contact date is inserted if by any chance the appointment is cancelled or not attended.

Just trying to make it little more clearer................................

Open the recordset “tablename” sorted by Referral_Reference_Num

This is a report to show the how many days a patient has waited after an appointment or contact with the clinic. So basically if the appointment is moved to another date due to reasons by the Clinic, the waiting list is to be calculated from the last date of contact of the patient with the clinic.
If the appointment has been re-scheduled because of reasons by the patients then the waiting list is reset and it should start counting from the day the patient rescheduled the appointment.

There should be two loops – one to loop through all the records in the table.  And the other to loop through records where the referral numbers are the same,.



Open the recordset “tablename” sorted by Referral_Reference_Num

Start Loop1 – to go through all the records in the table
Start Loop2- to go through all the records with the same referral_reference_num
Check the referral_reference_num. For the same referral_reference_num check the ‘status’
For
Status = 711 (Scheduled) insert the wardstartdate of the previous row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the referral_dte is inserted in the Wardstartdate.)
Status = 712 (In Progress) insert the wardstartdate of the previous row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the referral_dte is inserted in the Wardstartdate.)
Status = 713 (Attended and Seen) insert the contact_date of the same row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the contact_date (same) is inserted in the Wardstartdate). Come out of Loop1 and check for the next referral number.
Status = 714 (Attended and not seen)  insert the wardstartdate of the previous row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the referral_dte is inserted in the Wardstartdate.)
Status = 715 (Did Not Attend) insert the contact_date of the same row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the contact_date is inserted in the Wardstartdate.)
Status = 716 (Cancelled by Patient) ) insert the contact_date of the same row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the contact_date is inserted in the Wardstartdate.)
Status = 712 (Cancelled by Provider) insert the wardstartdate of the previous row to the wardstartdate. (if it’s the 1st record for that referral_reference_num then the referral_dte is inserted in the Wardstartdate.)
Break the loop and check for the next referral_reference_num.

For End of File. Close the table.

As I see your issue there are only 2 cases
case 1
Status 711,712,714,712
Copy previous Wardstartdate unless 1st record then copy referral_dte

Case 2
Status 713,715,716
insert contact_dte into wardstartdate

Is this not correct?
Jag5x5

That seems to work fine, except for the first case. when I run the code, the status 711, 712, 714, 717 gives me the value of  00:00:00 in the wardstartdate column.
Also can I ask, just out of quriosity why we use "#1/1/1900# "

Have we set any value for prefnum.

Hi Jag5x5,

I have set the prefnum and changed the prefnum<>rs.Fields("waitstartdate") to rs.Fields("REFERRAL_REFERENCE_NUM"). I think we are almost there except that in the cases where the status is "711", "712", "714", "717" it need to show the wardstartdate as the value in the previous wardstartdate if the prefnum is the same. if the prefnum is not the same as the earlier record, it need to set the wardstartdate to the referral_dte.
the rest of the code seems to work fine...

Hi Jag5x5,
I was waiting for some more fine tuning, but in the end I managed to finish it up.
Thanks a lot for your valuable guidance. It was really helpful. I am assigning the full points to you.

Thanks a lot.

Sorry been offline  couple of days thanks for he points.
so I use #1/1/1900# because I know that is a valid date for the code but invalid for us.  That way an error sticks out like a sore thumb.  Also you should set the prefnum to 0 and the pwtstdt to 1/1/1900 before you start the loop.

Public Function update_wait()
Dim rs As DAO.Recordset
Dim Pwtstdt As Date, prefnum As String
Set rs = CurrentDb.openrecordset("SELECT t.referall_reference_num, t.referalldte, t.contactdte, t.status, t.waitstartdate FROM tablename AS t ORDER BY t.referall_reference_num")
pwtstdt =#1/1/1900#
prefnum = 0
Do
rs.Edit
 Select Case rs.Fields("status")
        Case "711", "712", "714", "717"
               If Pwtstdt = #1/1/1900# Or prefnum <> rs.Fields("referall_reference_num") Then Pwtstdt = rs.Fields("referalldte")
               rs.Fields("waitstartdate") = Pwtstdt
         Case "715", "716", "713"
               If Pwtstdt = #1/1/1900# Or prefnum <> rs.Fields("referall_reference_num") Then Pwtstdt = rs.Fields("contactdte")
               rs.Fields("waitstartdate") = rs.Fields("contactdte")
End Select
prefnum = rs.fields("referrl_reference_num")
rs.Update
rs.MoveNext
If rs.EOF Then Exit Do
Loop
rs.Close

End Function

That way each loop sets the prefnum so that we know which one we are working with now it will set the date correctly and switch the date if it is the first record otherwise it will stick with the date it has.


HTH
Jag5x5

Thanks for the reply, Jag. The whole thing is working well. I just wanted to add a little bit of functionality more to it. I am not sure if I am asking too much. If you want me to post this as a new post and assign points for it I can.
This is my problem. From the table I had also added a colomn which will show me whether the patient is seen or is waiting. For all the cases except 713, I have set it up as waiting. Now my table is looking like this.

REFERRAL_REFERENCE_NUM      WaitStatus      WaitIndicator
168927      Waiting      N
168934      Waiting      N
168934      Waiting      N
168934      Waiting      N
168934      Waiting      N
168934      Seen      Y
168934      Waiting      N
168944      Seen      Y
169006      Seen      Y
169006      Seen      Y
169006      Seen      Y
169006      Waiting      N
169006      Waiting      N
169018      Waiting      N
169018      Seen      Y
169018      Waiting      N
169018      Seen      Y
169018      Seen      Y
169018      Waiting      N
169019      Seen      Y
169019      Seen      Y
169019      Seen      Y
169019      Waiting      N
169019      Waiting      N
169026      Waiting      N
169026      Waiting      N
169028      Waiting      N

REFERRAL_NUM     WaitStatus     WaitIndicator
168927                 Waiting     N
168934                 Waiting    
168934                 Waiting    
168934                 Waiting    
168934                 Waiting     N
168934                    Seen     Y
168934                    
168944                    Seen     Y
169006                    Seen     Y    
169006                    
169006                    
169006                    
169006                
169018               Waiting      N
169018                  Seen       Y
169018                
169018                
169018                
169018               Waiting      N
169019                 Seen        Y
169019                
169019              
169019            
169019            
169026            Waiting        N
169026             Waiting       N
169028             Waiting       N

I have changed the code a bit so that now as soon as status 713 is reached, it come sout of the loop and searches for the next referral num. and all other status will leave " waiting " in the Wait status column. Now I want to go to the last record for the same referral num which has the  waitstatus as "waiting" and assign "N" to the WaitIndicator as shown in the table above. In some cases it could also be the only first and only record for that referral num. In this way I can calculate the waiting time from the wait start date to the wait end date of a particular referral number.

As I said if you require me to post this as a new post. Please advice me.

For your reference..this is the code

Dim rs As DAO.Recordset

Dim Pwtstdt As Date, prefnum, pstat As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM waiting_table ORDER BY REFERRAL_REFERENCE_NUM")

pstat = Nz(rs.Fields("waitstatus"))

 

 

Do

 

rs.Edit

 

 Select Case rs.Fields("status")

         Case "711", "712", "714", "717"

               If prefnum <> rs.Fields("referral_reference_num") Then Pwtstdt = rs.Fields("referral_dte") Else

               rs.Fields("waitstartdate") = Pwtstdt

               pstat = "Waiting"

               rs.Fields("WaitStatus") = pstat

 

         Case "715"

               If prefnum <> rs.Fields("referral_reference_num") Then Pwtstdt = rs.Fields("contact_date") Else

               Pwtstdt = rs.Fields("contact_date")

               rs.Fields("waitstartdate") = Pwtstdt

               pstat = "Waiting"

               rs.Fields("WaitStatus") = pstat

               

        Case "716"

               If prefnum <> rs.Fields("referral_reference_num") Then

                    Pwtstdt = rs.Fields("cancel_date")

                    Else

                    Pwtstdt = rs.Fields("cancel_date")

                    rs.Fields("waitstartdate") = Pwtstdt

               End If

                pstat = "Waiting"

                rs.Fields("WaitStatus") = pstat

                'rs.Fields("WaitIndicator") = "N"

 

   

         Case "713"

               If prefnum <> rs.Fields("referral_reference_num") Then

               Pwtstdt = rs.Fields("referral_dte")

               rs.Fields("waitstartdate") = Pwtstdt

               rs.Fields("waitenddate") = rs.Fields("contact_date")

               pstat = "Seen"

               rs.Fields("WaitStatus") = pstat

            Else

           

               rs.Fields("waitstartdate") = Pwtstdt

               rs.Fields("waitenddate") = rs.Fields("contact_date")

               pstat = "Seen"

               rs.Fields("WaitStatus") = pstat

               

            End If

               

End Select

 

prefnum = rs.Fields("referral_reference_num")

rs.Update

 

If pstat = "Seen" Then

    Do While prefnum = rs.Fields("REFERRAL_REFERENCE_NUM") And Not rs.EOF

        rs.MoveNext

    Loop

rs.MovePrevious

End If

 

rs.MoveNext

 

If rs.EOF Then Exit Do

Loop

rs.Close

End Function

 

 

sounds to me like you are tryin to reinent the wheel.  you could calculate the wait time in SQL just fine.

I.E  select min(contact_date) wtst, max(contact_date) wtend, datediff("nn",max(contact_date),min(conatact_date)) wtlen from waitingtable where referal_reference_num in(select referal_reference_num from waitingtable where status in(713))

If you wnt to send me a sample of your db I can look at your code and maybee simplify some of this stuff for you.

HTH
Jag5x5

Thanks a lot, Jag
I finally did it using SQL, and got through what we needed. I am really grateful for your help.

Regards,

Jerry Peter