Help with Allen Browne's Recurring Events (1 Viewer)

[Elpidoforos]

Hello and good day to all.

I am creating a database that's used for scheduling events. I've used Allen Browne's sample database as a starting point for creating recurring events.
see: allen browne website /apprecur

I've added some SQL to also be able to schedule events every 1st, 2nd, 3rd or 4th monday or tuesday of the month etc.

These are the problems I've encountered so far:
1.
because the event dates are calculated on the fly I do not understand how to best save history of a particular event in case of changes made to the main (not instance of) event.
In my database I want to link employee's to events and I want to be able to calculate work times. now if I decide to assign another employee all historic data is lost and the newly assigned employee seems to have suddenly worked many many hours already while the previously assigned one has lost all his hours.

2. how can I possibly prevent clashes/conflicts in recurring events?
Let's say we have two events we want to schedule, both want to make use of conference room 109. The one event starts on a monday and is repeated every 7 days and another event is scheduled to take place every 4th monday of the month.
how can I schedule in both of these events and prevent getting conflicts or how do I make those potential conflicts visible as soon as I enter the values for a new event?
is it possible to perhaps prioritize one event over another?


Thanks for looking at my questions, I appreciate all the help I can get!

Greetings Elpi

 

[jdraw]

Tell us more about your application and database.
Did you download Allen's database?
What modifications, if any, have you made? We need something to work with --details, messages....
Do you have test data? Do you have a data model? Have you listed your business rules?
Can you test your test data against the data model?

 

[Elpidoforos]

Well i wont go over my entire database since it's still in practise phase and contains alot of unneccesary stuff but what I've done basicly is:
I've downloaded allen browne's "apprecur" sample database.
added a table DayOfWeek containing text: 1st, 2nd, 3rd, and 4th with numeric ID's 1 - 4
and a table DayOfMonth sunday ID 0 monday ID 1 tuesday ID 2 etc.
linked those tables with table Event
added fields dayofmonth and dayofweek to the EventCartesian Query and changed SQL for qryEventDates to the following:

IIf([IsCanned];Null;IIf(IsNull([DayOfMonth]) Or IsNull([DayOfWeek]);IIf([IsCanned];Null;IIf(IsNull([tbl_PeriodeType].[PeriodeType_ID]);[tbl_Event].[EventStart];DateAdd([tbl_PeriodeType].[PeriodeType_ID];[Instance_id]*[periodefreq];[eventStart])));DateSerial(Year(DateAdd("m";[instance_ID];[EventStart]));Month(DateAdd("m";[Instance_ID];[EventStart]));[DayOfMonth]*7)-IIf(Weekday(DateSerial(Year(DateAdd("m";[Instance_ID];[EventStart]));Month(DateAdd("m";[Instance_ID];[EventStart]));6-[DayOfWeek]))=7;0;Weekday(DateSerial(Year(DateAdd("m";[Instance_ID];[EventStart]));Month(DateAdd("m";[Instance_ID];[EventStart]));6-[DayOfWeek])))))


I did create a datamodel originally but after finding out that events can be repeated I've gone in a different direction. Now I'm deciding wheter or not to implement recurring Events or just have them manually enter each seperate event which will spare me alot of work but undoubtedly increasing their workload

not sure if I added anything else.
it's just that if I make any changes to the original event it's gonna not remember the original right?
like if I've linked an employee to an event it's not gonna remember that once I decide to link another employee to that event instead of the original one (or ProjectTeam for that matter if you already thinking about datamodels normalizing)

I would've been happy to post my full database had I not written in Dutch

 

[jdraw]

You kknow your situation better than any of us, so it isn't for a reader to say this is better because....
If we knew what the big issues were, how they develop.. then maybe someone has experienced similar and can advise.

If you are building some automation with a database, and you know at this point that it will increase the manual effort by users, then I assure you that is likely to fail --won't be accepted. Can you address that manual effort --build some pieces that would simplify the "manual part"?

Some times queries behind buttons on a form will reduce manual scanning/review of things. There must be some things that can ease the manual part. Perhaps identify the manual things; identify the most "painful" and address those, especially if you feel automated collision avoidance is not appropriate at this time.

As for what the database "remembers", it depends on design and what you put in which tables.

Good luck.

 

[Elpidoforos]

@jdraw

well it's supposed to be a very simple database.
The organisation that hired me (non profit), currently does all planning on excel sheets that they interchange with each other through email and what not.
the plan is to make a database that ensures people work with the same data so they want to store it in a database.
information is to be stored about Events, Employees and rooms.
each event has a date and start and end time and makes use of room(s) and employee(s).

Rooms and employee's can be booked for only one activity at a time.

simple enough right?
the biggest problem I thought there would be is flexibility because employee's have very irregular availabilities like for example: "every other wednesday from 16:00 to 18:00"

but these recurring events seem like an ever bigger problem for me to tackle.

 

[jdraw]

You have done a good job of identifying the major subjects involved.
I'm going to suggest a tutorial that will lead you through the steps to create a model based on your description and business rules.

Work through the tutorial to get your tables and relationships set up to support your business. Then come back and tell us/show us what you have and what issues you're facing. Don't jump into the details of Access until you have your tables designed.

The tutorial.

Good luck

 

[jdraw]

Looking at your description in more detail, I see these "rules"

Events, Employees and rooms.

An event has a start and end date-timestamp
An Event uses 1 or more Room(s)
An Event uses 1 or more Employee(s)
Rooms and employee's can be booked for only one activity at a time.
An Employee is available at certain times only
An Employee can be booked for 1 Activity
A Room can be booked for 1 Activity

You haven't said, but it appears that
An Event is composed of 1 or more Activity(s)


Are the Activity(s) all done in separate Room(s) -- that is can multiple Activities be in the same Room?

I have attached a preliminary model based on these rules.
Adjust as necessary. Booking could be Reservation, Assignment or whatever makes sense in your terms.

Good luck with your project.

 

[Elpidoforos]

Thanks jdraw for helping.
Im not sure if I understand your solution, in the ERD I see available date in tables?
Doesn't that mean i would get huge freaking tables that contain every date from now up too lets say 2020 or 2030 however long i wish to plan recurring events ahead?
isn't it a lot easier to calculate unavailable dates and either calculate conflicts based on dates that are already in use or query unavailable dates from a table like holidays?
I would think storing unavailable dates would take less effort and use less space than storing all available dates/times?

right now I use conditional formatting to show if a date & room is booked double. also I can calculate the number of conflicting dates immediatly when entering a new recurring event and decide wheter or not to reschedule based on number of created conflicts
I know it's not perfect but i just dont see how i can do it any other way that is more perfect

I'll try testing the setup you provided but right now I just cant see how it will help

anyway thanks.

P.S.
Events and activities in my database are one and the same.
Allen Browne uses Event and I think in my database I just renamed to Activity because for what I will be using they are usually very short and only for a select group of people. the word activity matches better.

 

[jdraw]

I was not giving hard and fast rules necessarily I was taking pieces from your description of the "business" and attempting to identify the Subjects involved and how they seemed to relate to each other. My effort was an "educated guess" as to a possible model. A model that you could review and adjust to ensure it actually reflected the business at your detailed level.

You will have to adjust/reconcile Event and Activity -one will have to go since they are the same thing. Adjust the model and remove Event, and adjust the relationships as necessary. Use your terminology, make it make sense to you.

As for availability I was just identifying the concept --if the Room is already busy at a particular Date_Time, then you can't use it for another Activity. The same with Employees --if some one is scheduled/booked for 1-4 on this Date, then you can't double book them. If for you it makes sense to identify a person's unavailability, then use that. You just need some consistent way to see who and/or what(Room) is free(not yet booked for a particular date/Time slot) before making a booking/reservation.

There is another concept at play here as well. If you are starting with a blank slate and you have to schedule say 5 Activities, which of those 5 should be booked first; which second etc. There is some sort of prioritization scheme at play, even if unspoken. Something or someone
has to say Activity X "takes priority over" Activity W, and Activity X gets booked while Activity W has to take another slot that isn't yet Booked.

Get some test data and try a few things; then adjust as necessary when a problem is found.

Good luck.