If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
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. |
#2
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
hi Jeff,
Jeff Gaines wrote: 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 -- |
#3
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
Jeff, see if this is any use:
Recurring events - how to handle recurring events that may never end at: http://allenbrowne.com/AppRecur.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jeff Gaines" wrote in message ... 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. |
#4
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
On 30/12/2008 in message Stefan
Hoffmann wrote: 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 Damerham Hampshire UK If you ever find something you like buy a lifetime supply because they will stop making it |
#5
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
On 30/12/2008 in message Allen
Browne wrote: 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. -- Jeff Gaines Damerham Hampshire UK The facts, although interesting, are irrelevant |
#6
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
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 . Steve "Jeff Gaines" wrote in message ... 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. |
#7
|
|||
|
|||
Steve the Sleaze just does not get it!
"Steve" wrote in message
m... 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 . 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 |
#8
|
|||
|
|||
Keeping A Diary/Appointments in Access DB
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,be ginDate,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 "Jeff Gaines" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|