Buildling A "Scheduling" Database (1 Viewer)

Dear Access Experts,

I am trying to build a system where candidates for a position will log in and, based on manager availability, select appointment times for interviews. Managers will be able to enter their availability (or lack thereof) by logging in as a manager.

Of course I will have all sorts of constraints, as each candidate will have to be interviewed by all three managers, etc, but I have basic design issues.

At first, I thought I should break the time into "slots" and have each manager input their availability for each slot with a checkbox. This can work, but I am motivated to try and find a way to give the managers the power to select ANY date and time range to be busy or free. I am told this will be more difficult but more desirable.

How do you recommend inputting availability for managers? Inputting when they are free, or when they are busy? As in table design, etc. the use of this database will be limited to allowing candidates to select from available time to make appointments, and to show managers their current schedule.

My thoughts: A manager would be limited in time to 9am to 5PM by default. Then, they would input when they want to be occupied. these would go to a table under "occupied". then, when a user is searching, results will be "any time between 9 and 5 that is not included in an entry on the "occupied" table.

The thing is, I would like to have managers input when they are available, rather than when they are not. So, if they say they are available from 12-2, I need to find some sort of date difference or calculation that generates the entries of 9-12 and 2-5 on the "occupied" table.

For the purposes of this project, do you think it is logical to base the table entries on occupied time or available time? I think occupied makes much more sense, as they are unchanging for the most part. an "available" entry of a time range would have to be chopped into two entries if an appointment was made in the middle of the period.


does anyone have any advice on this? I hope I made my aim clear enough.

Thank you,

MIke

I haven't used it before, but you can integrate an outlook calendar into Access, and I assume that'll deal with conflicting appointments and whatnot. Have a look at this:

http://msdn.microsoft.com/en-us/library/aa159619(office.11).aspx

My first reaction is why do you want to do this in Access? I can see very little reason from your initial question. What you want to do is exactly what Outlook does. I think I would be looking at creating a form in Outlook to handle this and then the results or any final results to be saved in access. Outlook can open a database and save results.

The OP is doing an Access course, but yes Outlook would be the normal way forward here.

I guess it would be most appropriate as part of a larger system.

Then the teacher should be shot (in my opinion). That like the music teacher telling them to use a calculator to make music.

It's a valid application for Access, I reckon, if you're talking about integrating it into a larger platform. And calulators are only good for writing BOOBS on anyway

That'll keep me busy for the rest of the day..... ha ha and 55378008

JamesMcS said:

It's a valid application for Access, I reckon, if you're talking about integrating it into a larger platform. And calulators are only good for writing BOOBS on anyway

That'll keep me busy for the rest of the day..... ha ha and 55378008

Click to expand...

So a hip music teacher might get them to do it on an iphone.

I'd have liked a teacher as cool as that! Mine all called me 'boy'

So have you had any experience with Outlook integration? Is the link I posted upstairs relevant, d'you think? (Sorry for hijacking your thread Mike)

Sorry I see now that the purpose here is to replace outlook.

mikesta21 said:

At first, I thought I should break the time into "slots" and have each manager input their availability for each slot with a checkbox. This can work, but I am motivated to try and find a way to give the managers the power to select ANY date and time range to be busy or free. I am told this will be more difficult but more desirable.

Click to expand...

I would suggest you look at how calendars such as outlook implement this. 1. you then have a model to work off and 2. creating a similar user interface will make it more user friendly.

First I could imagine a form with a date or range of dates at the top and then check boxes representing either half hour or full hour slots. The manager clicks the slots he is available for the date or range of dates.

mikesta21 said:

Inputting when they are free, or when they are busy?

Click to expand...

In reality you are dealing with managers, so choose the option which is less work for them.

mikesta21 said:

My thoughts: A manager would be limited in time to 9am to 5PM by default. Then, they would input when they want to be occupied. these would go to a table under "occupied". then, when a user is searching, results will be "any time between 9 and 5 that is not included in an entry on the "occupied" table.

The thing is, I would like to have managers input when they are available, rather than when they are not. So, if they say they are available from 12-2, I need to find some sort of date difference or calculation that generates the entries of 9-12 and 2-5 on the "occupied" table.

For the purposes of this project, do you think it is logical to base the table entries on occupied time or available time? I think occupied makes much more sense, as they are unchanging for the most part. an "available" entry of a time range would have to be chopped into two entries if an appointment was made in the middle of the period.

Click to expand...

I think (I am not that experienced compared to others in this forum) that I would have a table with a Date, time and manager column.

When the manager fills out the form as I suggested above you fill your table.

Eg Manager Jim says that on 18 May 2010 that he is available from 9-10 (on the form you have 1hr check boxes)

Thus your table would be filled like this below and you add a line for each 1 hour slot he is available.

2010-05-18 ..... 9-10 ..... Jim

Another way would be to have a Date, Manager column and a column for each 1 hour period. Then fill the 1 hour periods with True/false for the given date.

I hope this causes other people to comment on my suggestions which will help you.

PS: Data base design is something I am not experienced with and thus am still learning. So I am happy for people to pull my suggestions apart.

First I could imagine a form with a date or range of dates at the top and then check boxes representing either half hour or full hour slots. The manager clicks the slots he is available for the date or range of dates.

Click to expand...

I did think about this when replying to a simliar post the other day. The problem I foresaw was that you'd eventually run out of slots in the bookings table (I think the maximum is 255 columns), so you'd never be able to keep track of history.

Thinking about it some more, you could assume that the managers are only free for a certain time period, as Mike suggested way back up at the top. Then you could have a table with manager name, day and time slot as columns, making all three a PK. This way you would come up with a PK violation (which you could trap) if someone tried to book a slot which already existed in the table.

Then, you could use a crosstab query, defining the column headers (so Mon 9am, Mon 10am etc), and any date/time slots that didn't appear in the table above would simply appear blank.

Your booking form could be split - have text boxes/combo boxes at the top of the form for making reservations and the crosstab described above underneath, and it would sort of look like a calendar.... sort of....

I just realised the first half of this was described above by darbid - great minds think alike! (And fools seldom differ..)

In regard to managers booking time out - in the table described above you could have, say, a 1 for slot booked by a candidate and a 2 for managers to say they're not available. Then you could maybe have some conditional formatting to colour the cell in red if it's manager-booked time, or another colour if it's candidate-booked time.

Just thought I would interject here. In one of my apps we make appointments for patients to see specific consultants on specific days.

First of all when a Consultant is addedd to the database we setup their clinic times. (See Screenshot1 image below). Notice that they can have up to two clinics a day (am & pm). Also there is an option box that says "What shall we do if the user tried to book an an appointment to see the Consultant when in a time slot when he does not have a clinic.

So what happens if the user tries to book an appointment outside the clinic times the relevant action is taken. (See Screeenshot 3).

We also have an appointment viewer that lets us see which Consultants have appointments on a selected date and the patients they are seeing. (See screenshot2).

What I forgot to mention was Yes we could have done something similar with Outlook but you would need to install the Add-in on everyones machine to get it to communicate with Access. By handling it in the application negates this necessity.

DCrake said:

We also have an appointment viewer that lets us see which Consultants have appointments on a selected date and the patients they are seeing. (See screenshot2).

Click to expand...

Is that the outlook active x control?

No it is not. It is part of a suite of ocx's about 80+ in total

OK, here's a thought. The comments have been bandied about as to whether the manager should enter "available time" or "busy time." If you enter slots of available time, then you have to have a case where a non-manager changes a manager's available time - by reducing its size or removing it completely.

If you enter slots for busy time, you have two or more useful things. First, you can record and report on the manager's busy time, including any explanations given. Second, the applicant can only enter a whole entry that does nothing to existing entries. I.e. you can only enter an appointment at an unconflicted time and cannot modify the ranges of any conflicted times. Third, this is the model used by Outlook.

In passing, someone noted a comment about "no more than 255 slots".... Oh, PLEASE don't do it that way. Learn to normalize. Any method that gets involved with slot counts like that is guaranteed to be non-normalized. If you're gonna learn, learn it right.

To do a proper scheduling, the following comments might be relevant.

Suppose that you want a time slot from 9:30 to 10:00 on a given day. So you enter your request and click a button to qualify the times. A "no go" situation occurs when the following is true:

At least one existing slot reservation exists for which your start time is between the starting and ending times of the existing slot. (Look up the BETWEEN xxx AND yyy syntax to see how to do this).

At least one existing slot reservation exists for which your end time is between the starting and ending times of the existing slot. (BETWEEN again.)

Either of your times is between midnight and the chosen day's start of business. Actually, just a < comparison.

Either of your times is between the chosen day's close of business and midnight. This is just a > comparison.

Your start time exactly matches another start time.

Your end time exactly matches another end time.

Any existing request's start time is between your start and end times. Another BETWEEN case.

Any existing request's end time is between your start and end times. Another BETWEEN case.

Most of these can be done with a DCount to check for ZERO as the result. If any ONE of these is counts is NOT zero, you have a conflict.

Before you go nuts, you can build a query that selects only the already-scheduled items for a given day and can then limit your DCOUNT calls to this subset query, which would therefore be very fast.

If you were in the VBA mood, do your query to sort the busy slots according to start time and date, then keep on searching records until you find the first busy slot for which your end time is less than the start time of the busy slot. If it is a dynaset, you can do a .FindFirst for the relationship in question. Then do a .MovePrev to look at the start and end times of the previous busy slot. Knowing the two "flanking" records, either you overlap or you don't. End of discussion.

In passing, someone noted a comment about "no more than 255 slots".... Oh, PLEASE don't do it that way.

Click to expand...

I did say don't do it, I thought that was the way the OP wanted to go in the first place.

So to get around all the problems of overbooking/finding available slots, will my plan of PKing manager, date and time together not be any good? And having the booking table represented as a crosstab with defined column headers, so where there isn't a manager/date/time entry in the booking table, that cell will appear blank?

Wow! i subscribed to this thread but I only got the notification for the first one! I thought this was dead!

Sorry, thank you all for help!

First of all, yes this assignment is rediculous. My teacher is rediculous. He knows that, and just wants us to learn the program. He definitely is accomplishing his goal.

So, as far as having 1/2 hour "slots", that was my first idea. He will not give great points for this method, as you are assuming "start" and "end" time of the slots. I thought I would have some sort of date selector and then append one row per slot, so many many rows per day. At least this way, a query could be ran to find all records (start dates) that are still available. If I made one record/row have ALL of the time slots for a given day.. how would I call individual fields from a record based on field criteria rather than row criteria?

Let me rephrase that.. a friend of mine has already went down this path, and I don't agree with it: He has on his table as fields(columns): the date, and every time slot from start to finish. That way, one record represents a managers availability for the entire day. BUT: how would a user find only the fields within the record where it is yes? I only know how to query for records (rows), not a few columns within a row. I hope that made sense.


So, back to my potentially better idea:

So I would have a manager say "when are you free to take interviews". This time range would be turned into a two blocks of unavailable time, via some sort of "datediff" function calculating the difference between the start time of the business's open in the morning with the start time of the availability. The same goes for the difference btween the end time of the availability and the close of business. these "unavailable" slots will be recorded as a range and put on a table "start time, end time" of unavailability. Then, the user can see what time ranges are designated not available via some sort of graphic..


does this sound logical? God this is messy.. and no I cannot use some sort of tricks that would take the user outside of access using outlook.

PS: how do you manage to make your schedules show up as bars and colors?

i am in over my head!


EDIT: Docman: Thank you! I missed your post when I made this reply. I agree that "unavailable" time is much more logical to document, as once it is posted as unavailable it will not change, rather than available time which will constantly be changing. So, what do you recommend for table design? For fields: include manager_id, date, timestart, timeend? Would I set it up so ALL slots are inputted as seperate records, and if the person is available from 9-5 there would be one record per time slot? Or would you recommend just using one record per period? That is my main design concern.

See, I really want the flexibility of inputting any time, rather than having a slot. To be honest, I am told I will be rewarded much more points for doing it this way and I am determined to figure it out!!

Because of this desired freedom in start/end time, I think I should not use slots and just put up one record per block (of any length) of time inputted.

again, it is possible to calculate "occupied time" based on inputting "free time".. right?

please see my thread on visualizing thiis:

http://www.access-programmers.co.uk/forums/showthread.php?p=966363#post966363

OK so what I meant was - create a table with manager, date and time as fields. Set them all as a primary key. This will stop dupes. Now - in this table, you enter line by line the manager, the date, the time of the booking and a yes/no field to say 'this slot is booked'. Unbooked slots don't need to be in this table.

Now - create a crosstab query based on this table. Drag down manager as a row heading, date as a row heading, time as a column heading, the yes/no field as the value. (In the total row for this field change it to sum). In the crosstab's query properties, you can specify column headers - so put in "09:00", "09:30" and so on, you can divide the time up however you like. Run the query.

Assuming you've got data in that table, you should see a big grid, with manager and date down the side, times across the top, and a 1 in any time slots you've booked in the table. Bear in mind that to sppear in the correct column, the time will have to match the column heading name exactly - so 9:00 won't come up in 09:00, but 09:00 will.

You might have to mess around with finding the right way to name the columns, but that's the way I'd do this.

This does also mean that you can have a booking form - with text boxes and whatnot at the top to select manager/date/time and make the booking, and a subform based on this crosstab query underneath, so it looks like a proper calendar.