• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Appointment Scheduler (1 Viewer)

J

Jerry Stoner

Guest
Ive been contemplating building an appointment scheduler for some time now. Basically a form to enter customer name into appointment slots in a calendarlike format up to 1 year out from the current date. Also show a daily, weekly and monthly format.
The appointment slots by default 15 minutes but with option to expand in 15 minute increments as needed.
What Im having trouble grasping is a way to lay out a table structure to handle the 15 minute increments.
Can anyone give me a nudge in the right direction?
BTW all access no linking to outlook.
 

Travis

Registered User.
Local time
Today, 01:25
Joined
Dec 17, 1999
Messages
1,332
Make a single record be a 15 Min Block of time.

Basic Structure

Date | Time | Appointment
 

[nateobot]

Registered User.
Local time
Today, 03:25
Joined
Jul 15, 2002
Messages
64
One of your fields could be how many 15 chunks of time the appt takes?
 

RichMorrison

Registered User.
Local time
Today, 03:25
Joined
Apr 24, 2002
Messages
588
Jerry,

I did a similar app for scheduling conference rooms. The main table was:
Date,
Time,
Room

Date and Time formed the primary key. I made an auxiliary table with the relevant start times in 30 minute chunks and a table with all the Mon to Fri dates for a year. A query then populated the main table with a record for each Date/Time interval for a year.

The whole thing is displayed on a form that looks like a week-at-a-glance calendar. The users enter their name in a cell to reserve a room. If they want more that 30 minutes, they fill in several cells.

The user can page to the Next or Previous day on the form.

It is not sophisticated but it does work.

HTH,
RichM
 
J

Jerry Stoner

Guest
Wow that was fast!

Thanks all.
Rich, so if I want to go out say five years I need a table with all the dates for however long I want right?
Then a daily,weekly, monthly and yearly query as rs for subforms with appropriate layout on a tabbed main form.
Next a calandar control on each subform to select a day, week, month or year.
Yeah I got it now. Thank you all for the"gentle nudge" toward what should have been obvious.
 
Last edited:

RichMorrison

Registered User.
Local time
Today, 03:25
Joined
Apr 24, 2002
Messages
588
you wrote
<<
Rich, so if I want to go out say five years I need a table with all the dates for however long I want right?
Then a daily,weekly, monthly and yearly query as rs for subforms with appropriate layout on a tabbed main form.
>>

Yup, you got it. I made a simple function that "walks through" a year from a user-supplied start date. This function inserts a row in a "Dates" table for each Mon-Fri date in a year.

A second table of "Time" was manually filled in with start times.

A third query includes Dates and Time and produces the "cartesian" product of the two. This query fills in an empty calenday for a year.

The form starts with all record for today and the user just moves forward with a command button. The code behind the button adds a day to some work date and requeries the form.

HTH,
RichM
 
J

Jerry Stoner

Guest
Yeah Rich did the same thing last night. I looped 10k times and now have a table that contains every day out to dec 2028(I nedeed to include Sat and Sun)
Going to start building ther rest now. Ill let you know if I have any problems but I think its pretty straightforward from here.
 
R

Rich

Guest
Except that you have a table full of dates for which there may be many with no associated records
 
J

Jerry Stoner

Guest
You just lost me Rich? The way i saw it is tblDate containing Dates, tblAppTimes containing 15 minute intervals, tblCust (obvious) and tblApp contaning CustID, Date and AppTime.
The form will be tabbed Daily which will have 15 min intervals to select, Weekly which will be in a crosstab format days accross top and intervals downward, and Monthly which will look like a calander each day being a cmdButton which when clicked will show the daily form. Also navigation to jump to a specific day/week/Month.
I know this will get complicated but please if I'm missing something here give a shout before I waste a ton of time on this. I've no client looking for this. I'ts just a project I'm curious about building however if I can pull it off I'm sure It'll come in handy.
 

RichMorrison

Registered User.
Local time
Today, 03:25
Joined
Apr 24, 2002
Messages
588
I understand what Rich is saying and it's true.

The appointment table contains a lot of rows that will never be filled in.

Given the current cost of disk storage, this will cost you and least 5 cents a year :) (5 pence in UK I think).

RichM
 
J

Jerry Stoner

Guest
Well I think I can live with that.
The structure I outlined is sound though, right?
 
J

Jerry Stoner

Guest
One Step Further

So... let's assume this is for a business that provides a service and there is more than one person who can provide that service at any given time. So now I need to be able to schedule multiple appointments at any given 15 minute interval based on the provider. tblProvider, tblProviderSchedule?

What I'm thinking is I need a seperate table for each "provider" showing all time slots with the customers allready scheduled showing in the appropriate time slots thus alowing new appointments to be scheduled.

So that means I need frmProvider with a lookup, sfrmSchedule for EACH provider. I also need a master table with schedule dates and if a new provider is brought on board a module that will create a table for that new provider.

So now I've got x number of tables containing 15 minute intervals for 10 years. Now we are talking a ton of unused records.

So I guess the question is.. is there a better way to structure this or am I going to have to eat a large DB with x number of tables with 400k or so records many of which will be empty?

Later I'll need to deal with possible multiple locations as well and room availability per location but that is a nightmare for another night.

Opinions, ideas and sarcastic remarks greatly appreciated.

Jerry
 

RichMorrison

Registered User.
Local time
Today, 03:25
Joined
Apr 24, 2002
Messages
588
Well Jerry, the conference room reservation app I made actually reserves several room. So each record is:
Date,
StartTime,
Room 1,
Room 2,
Room 3
etc.

This is not a normalized structure, but since I don't do any reporting on a specific conference room I don't really care.

And yes this will increase the number of empty cells. So call it 10 cents a year.

RichM
 
J

Jerry Stoner

Guest
Yeah this does seem to be a case where denormalized will work better. Thanks again!
 

John.Woody

Registered User.
Local time
Today, 08:25
Joined
Sep 10, 2001
Messages
354
I know this may be coming to the party a bit late but back in the days of Access version 1 (there I go again showing my age) microsoft supplied a sample db personal information manager with built in appointment scheduler. Its too big to post here but I've put a copy at:

http://www.msmltd.fsnet.co.uk/dlPim.htm

If you're interested. I've upgraded it to Access 97 - not used it myself but I think most of the buttons work.

HTH
John

Oh I forgot Login name John Doe, Password John
 
Last edited:

Noreene Patrick

Registered User.
Local time
Today, 03:25
Joined
Jul 18, 2002
Messages
223
The appointment scheduler is exactly what I need!!!! However, I dont need the time tables only dates. I will be using entire days.

What I have is a table with Empname, shift, hours off, startdate, enddate. I have set up db to track employees requesting time off.

I have a form that fills in the table.

But, I dont know how to make the dates automaticcally fill in between startdate and enddate.

And, I also dont know how to layout the report to show 7 days at a glance with the button for forward and back.

I tried to download the file Pim2.exe file, but i got an error,

Could someone please help me??? (But, be aware that I am a novice that is beginning to love ACCESS!!!)

Thanks in advance.
 
J

Jerry Stoner

Guest
Next issue

Ok Ive got a daily schedule made for up to 10 people with 15 minute intervals for the day.

Next issue is a weekly form in a "crosstab" format but not really a crosstab since it must be updateable. Im thinking a form showing Monday through Friday with 7 subforms one for each... well you get the idea.

This query shows 7 days beginning with today.
SELECT DISTINCT TOP 7 tblDate.AppDate
FROM tblDate
WHERE (((tblDate.AppDate)>=Date()))
ORDER BY tblDate.AppDate;

How do I get the query to show 7 days beginning with Monday of the current week?
 

Users who are viewing this thread

Top Bottom