A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

schedule recurring events an access database



 
 
Thread Tools Display Modes
  #1  
Old February 1st, 2008, 08:54 PM posted to microsoft.public.access.tablesdbdesign
Amy
external usenet poster
 
Posts: 412
Default schedule recurring events an access database

I am trying to create an access database that will show upcoming appointments
with clients. These appointments are always recurring every month, 3 months,
or 6 months. I would like to enter the appointment and client information
into a table one time and then see the appointments when I open a weekly or
monthly report. Also, I want to mark the appointment complete in my table
after it is held so that a meeting that is scheduled but not held will
automatically show on the next week's calendar.

I have created several other somewhat complex databases in Access for my
company, but I am having trouble with this one. Any help that you can offer
on any of these points would be GREATLY appreciated!!!!!
Thanks
  #2  
Old February 2nd, 2008, 02:16 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default schedule recurring events an access database

As you found, recurring events are not the simplest thing on the planet.

The simplest approach is to create a table with fields like this:
EventID primary key
StartDate Date/Time date of the first appointment. Requiried.
PeriodType Text "d", "m", "q", or "yyyy". Required.
Freq Number how many periods between events. = 0.
EventCount Number how many events in the series.

Next, you need a counting table from 1 to the highest number of repeating
appointments you could need (perhaps 1000?) This table has one field named
(say) CountID, type Number, primary key, and you save the table as (say)
tblCount. Enter record from *zero* to the highest number. The code in this
link can populate the table for you:
http://allenbrowne.com/ser-39.html

Now create a query using both tables, but with *no* join between them in the
upper pane of table design. This gives you every possible combination (a
Cartesian product), which we will use to get a record for each appointment
in the series.

In the Criteria row of the query under CountID, enter:
[EventCount]
This limits the query to the right number of events for the series.

Now enter an expression like this in the Field row of the query:
EventDate: DateAdd([PeriodType], [CountID] * [Freq], [StartDate])
This gives you the date of each event in the series.

This approach is fully normalized and quite easy to implement. The major
limitation is that you cannot remove or reschedule one appointment in the
series. If you need that functionality, you may be able to do that with a
table of exceptions that uses an EventID and CountID and EventDate, so you
can switch the n-th appointment for a particular event to a different date
(or to Null for no date), and then outer join the above query with the
exception table to get the alternative date.

If that's not powerful enough, you can actually generate a record in a
related table for each event in the series. This gives great flexibility but
has several drawbacks:
- Doesn't cope with unlimited series (no end date)
- Harder to maintain. For example, if a change is made to the series, how to
you cascade that change to all events in the series, when some appointments
may be specific (out of the normal series)?

--
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.

"amy" wrote in message
...
I am trying to create an access database that will show upcoming
appointments
with clients. These appointments are always recurring every month, 3
months,
or 6 months. I would like to enter the appointment and client information
into a table one time and then see the appointments when I open a weekly
or
monthly report. Also, I want to mark the appointment complete in my table
after it is held so that a meeting that is scheduled but not held will
automatically show on the next week's calendar.

I have created several other somewhat complex databases in Access for my
company, but I am having trouble with this one. Any help that you can
offer
on any of these points would be GREATLY appreciated!!!!!
Thanks


  #3  
Old February 4th, 2008, 06:18 AM posted to microsoft.public.access.tablesdbdesign
Amy
external usenet poster
 
Posts: 412
Default schedule recurring events an access database

Thank you for the info, I will try your suggestions. (I like to punish myself
)
I love using Access for data management, but is there another program that
you can recommend that will do the job if I can't get this done? I like the
way that calendar programs like Outlook's calendar work, but I need to
perform searches and queries.

Thanks
Amy

"Allen Browne" wrote:

As you found, recurring events are not the simplest thing on the planet.

The simplest approach is to create a table with fields like this:
EventID primary key
StartDate Date/Time date of the first appointment. Requiried.
PeriodType Text "d", "m", "q", or "yyyy". Required.
Freq Number how many periods between events. = 0.
EventCount Number how many events in the series.

Next, you need a counting table from 1 to the highest number of repeating
appointments you could need (perhaps 1000?) This table has one field named
(say) CountID, type Number, primary key, and you save the table as (say)
tblCount. Enter record from *zero* to the highest number. The code in this
link can populate the table for you:
http://allenbrowne.com/ser-39.html

Now create a query using both tables, but with *no* join between them in the
upper pane of table design. This gives you every possible combination (a
Cartesian product), which we will use to get a record for each appointment
in the series.

In the Criteria row of the query under CountID, enter:
[EventCount]
This limits the query to the right number of events for the series.

Now enter an expression like this in the Field row of the query:
EventDate: DateAdd([PeriodType], [CountID] * [Freq], [StartDate])
This gives you the date of each event in the series.

This approach is fully normalized and quite easy to implement. The major
limitation is that you cannot remove or reschedule one appointment in the
series. If you need that functionality, you may be able to do that with a
table of exceptions that uses an EventID and CountID and EventDate, so you
can switch the n-th appointment for a particular event to a different date
(or to Null for no date), and then outer join the above query with the
exception table to get the alternative date.

If that's not powerful enough, you can actually generate a record in a
related table for each event in the series. This gives great flexibility but
has several drawbacks:
- Doesn't cope with unlimited series (no end date)
- Harder to maintain. For example, if a change is made to the series, how to
you cascade that change to all events in the series, when some appointments
may be specific (out of the normal series)?

--
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.

"amy" wrote in message
...
I am trying to create an access database that will show upcoming
appointments
with clients. These appointments are always recurring every month, 3
months,
or 6 months. I would like to enter the appointment and client information
into a table one time and then see the appointments when I open a weekly
or
monthly report. Also, I want to mark the appointment complete in my table
after it is held so that a meeting that is scheduled but not held will
automatically show on the next week's calendar.

I have created several other somewhat complex databases in Access for my
company, but I am having trouble with this one. Any help that you can
offer
on any of these points would be GREATLY appreciated!!!!!
Thanks



  #4  
Old February 4th, 2008, 07:53 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default schedule recurring events an access database

Outlook is the obvious one.

I'm sure there are plenty of others.

It's not too daunting in Access. :-)

"amy" wrote in message
...
Thank you for the info, I will try your suggestions. (I like to punish
myself
)
I love using Access for data management, but is there another program that
you can recommend that will do the job if I can't get this done? I like
the
way that calendar programs like Outlook's calendar work, but I need to
perform searches and queries.

Thanks
Amy


  #5  
Old February 8th, 2008, 02:46 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default schedule recurring events an access database

In case you are still following this thread, here's a new article:
Recurring events - how to handle recurring events that may never end
at:
http://allenbrowne.com/AppRecur.html

Includes a sample database to download and pull apart.
The whole thing is done with queries (no code except for the interface.)
Works with Access 2000 and later.

--
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.

"Allen Browne" wrote in message
...
Outlook is the obvious one.

I'm sure there are plenty of others.

It's not too daunting in Access. :-)

"amy" wrote in message
...
Thank you for the info, I will try your suggestions. (I like to punish
myself
)
I love using Access for data management, but is there another program
that
you can recommend that will do the job if I can't get this done? I like
the
way that calendar programs like Outlook's calendar work, but I need to
perform searches and queries.

Thanks
Amy


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.