Keeping A Diary/Appointments in Access DB

[Jeff Gaines]

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.

 

[Stefan Hoffmann]

hi Jeff,

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.

You only have to check whether it is the same weekday.

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.

The classic way is to calculate the recurring appointments in your
application.



mfG
--> stefan <--

 

[Allen Browne]

Jeff, see if this is any use:
Recurring events - how to handle recurring events that may never end
at:
http://allenbrowne.com/AppRecur.html

 

[Jeff Gaines]

The classic way is to calculate the recurring appointments in your
application.

Many thanks Stefan

I have added a couple of extra fields to the table and I am fine tuning my
algorithms!

 

[Jeff Gaines]

Jeff, see if this is any use:
Recurring events - how to handle recurring events that may never end
at:
http://allenbrowne.com/AppRecur.html

Many thanks Allen

I have down-loaded the DB and will study it. I find it easier to do the
calculations in C# but I will have a look at your combined approach to see
if I can speed things up.

 

[Steve]

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
(e-mail address removed).

Steve

 

[John... Visio MVP]

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 (e-mail address removed).

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many excellent Access developers who gladly help for FREE. Stevie
of course is NOT one of them. He has proven many times in the past that he
really does not know what he is doing. His only purpose is to try and
seperate posters from their money. If he was any good, his repeat business
should be enough to keep him from grovelling for scraps from these
newsgroups.

John... Visio MVP

 

[Bruce Meneghin]

Here is a table structure with example data for storing any kind of rucurring
event. It handles a few more cases than the most excellent AllenBrowne
method. I have companion code that searches this table and generates
specific events between a range of dates passed to the function. I can
provide the code if this seems like it will suit your needs.

ID,frequency,MonthName,DayName,number,eventName,beginDate,endDate,skip
1,monthly,,Tue,3,3rd Tuesday of each month,1/1/1900,1/1/2999,0
2,weekly,,Mon,,every Monday,1/1/1900,1/1/2999,0
3,yearly,Dec,,25,same month and date each year (Christmas),1/1/1900,1/1/2999,0
15,weekly,,Mon,,Monday every two weeks (payday),6/2/2008,1/1/2999,1
18,yearly,Nov,Thu,-1,last Thursday of Nov. (Thanksgiving),1/1/1900,1/1/2999,0
19,monthly,,,15,15th of each month,1/1/1900,1/1/2999,0
20,monthly,,Fri,-1,"semi-annual, last Friday of Dec &
Jun",12/28/2007,1/1/2999,5
21,yearly,Sep,Mon,1,"1st Mon in Sep, every 3 years",1/1/2000,1/1/2999,2