The "classic" way of recording appointments would be something like:
TblAppointmentWith
AppointmentWithID
ClientID
<other fields as needed>
TblAppointment
AppointmentID
AppointmentWithID
AppointmentDate
<Other descritive fields about an appopintment>
You would create a form/subform where the main form would be based on
TblAppointmentWith and the subform would be based on TblAppointment.
In your context in your post, you are missing an essential piece of data, ie
the number of recurring weeks. You are also assuming that all appointments
are the same day of the week. If you have a field, NumRecurWeek, in your
appointment record, you can calculate in a query the last appointment date
using:
DateAdd("d",7*NumRecurWeek,[AppointmentStartDate]). Then if you want to know
if an appointment should be displayed on a specific date, all you need do is
check if the specific date is before the last appointment date.
As a side note.... I have developed a calendar form and a calendar report in
Access which could be implemented in your database if you took the "classic"
way of recording appointments. The calendar form and calendar report look
just like a page from a calendar on the wall. The year and month of the
calendar are selectable. The calendar can dispaly different data each day so
the calendar could display your appointments each day. Appointments could be
added, edited and deleted with the calendar. Other functionality is easily
added to the calendar. I could add a calendar form and calendar report to
your application for a modest fee. If you are interested contact me at
santus[ at ]penn.com.
Steve
"Jeff Gaines" <whitedragon[ at ]newsgroups.nospam> wrote in message
news:xn0fzjad72q7uw8000[ at ]msnews.microsoft.com...
[Quoted Text] >
> I am writing an app in C# which keeps my diary appointments in an Access
> 12 DB. Accessing one off appointments is fine but I have to do a lot of
> calculations in respect of repeat appointments.
>
> If I have a weekly appointment that started on 1 Jan 2002 and I want to
> find out if it should be displayed today I loop through the record adding
> 7 to the start date until the date is => than the beginning of the current
> month. I them start another loop and create temporary records for each day
> of the current month until the date is > than the last day of the month.
> That gives me a list of records including the date on which the
> appointments occur in the current month. It works but it is a bit slow and
> will get slower as time goes on because it will have to loop for longer.
>
> Is there a better or 'classic' way of keeping recurring appointments in a
> DB that would cut down the work involved in the calculations? I could have
> a linked table that contains future appointment dates but I would have to
> decide how far to extend it.
>
> I would appreciate any thoughts or guidance.
>
> --
> Jeff Gaines Damerham Hampshire UK
> There are 10 types of people in the world, those who do binary and those
> who don't.
|