Calculating Recurring Appointment Dates

[JR]

Hi All,

I am interested in creating a report in Access that shows scheduled
recurring appointments based on a given start and end date, frequency (i.e.
every 1st, 2nd, 3rd, etc) and interval (i.e. days, weeks, months, etc). Has
anyone had a go at this before who might be able to offer some advice?


JR

 

[PC Datasheet]

You need tables like this:
TblFrequency
FrequencyID
Frequency

TblInterval
IntervalID
Interval

TblPerson
PersonID
FName
LName
FrequencyID
IntervalID

TblAppointment
AppointmentID
PersonID
AppointmentDate

Appointments are noy made with a report. A report prints out appointments
that have already been made; in this case the appointments in
TblAppointment. So you first need a process to create the appointment
records for a person in TblAppointment.

 

[JR]

Many thanks for that. I didn't word my original post well. I don't want a
report per se, but ideas on how best to calculate recurring appointments
based on user selected values in a form. Those values would be stored once
against the selected appointment and if the user wants to see how often they
will be visitng that client in the next 6 months (for example), a form (or
report) will be used to present the visits. As I said, these visit
"records" will be calculated, not stored.

For example, the user sets a recurring appt for John Doe starting on Jan 1st
2005, which will occur on every 2nd Friday until Dec 31st 2005. I want to
store the variables (frequency and interval/Day value) and calculate the
dates on which the appointments will fall in the given date range.

I hope that's a little clearer. All feedback gratefully reviewed.


JR

 

[JR]

For all it's worth, this is what I am currently doing. The values in this
sub will be passed from records in a table (I will add the recordset loop
later).

Public Sub RecurringAppointments(sDate As Date, _
eDate As Date, _
Frequency As Integer, _
ApptDay As Integer) 'Day of week value (1 to 7)

'Appointments on a specific day of the week at a set frequency.
'(e.g. Every 2nd Thursday in the date range.)

Dim strDate As String
Do
If Format(DateAdd("d", Frequency, sDate), "w") = ApptDay Then
strDate = DateAdd("d", Frequency, sDate)
Debug.Print strDate
End If
sDate = sDate + Frequency
Loop While sDate < eDate
End Sub

I'm interested to know if there is a smarter way to do this.

TIA

JR

 

[PC Datasheet]

Add the following table to the ones I previously:
TblRecurringAppointment
RecurringAppointmentID
PersonID
ReasonForAppointments
FrequencyID
IntervalID

Remove FrequencyID and IntervalID from TblPerson.

In TblAppointment, change PersonID to RecurringAppointmentID.

You now have a one to many relationship between TblPerson and
TblRecurringAppointment where each new reason to set up recurring
appointments can be entered in
TblRecurringAppointment. Then you have a one to many relationship between
TblRecurringAppointment and TblAppointment where appointments can be
recorded for a given reason for appointments. You need a form/subform to
enter a record in TblRecurringAppointment and you would then use VBA in the
subform's AfterUpdate event to automatically create the corresponding
appointment records in TblAppointments.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[JR]

Thank you very much for taking the time to think about this as you have. I
do already have a suitable table structure which is similar to what you have
suggested. The details of the appointment are already stored in an
appropriate table and do not change from one appointment to the next (which
is why I only need to calculate future dates, not actually store a new
appointment record).

I have posted the code I am currently using to process the appointment info
and calculate the dates. If you have any suggestions for change
(improvement) I would be keen to hear them.

Again, many thanks.

JR

 

[Duane Hookom]

There is a small sample database at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for
scheduling recurring events.

If you need a report for displaying a calendar or week-at-a-glance try the
calendar reports sample at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

 

[JR]

Great stuff as usual. Thanks Duane.


JR

There is a small sample database at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for
scheduling recurring events.

If you need a report for displaying a calendar or week-at-a-glance try the
calendar reports sample at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4