Next available appointment

[Guest]

Been struggling with this...

I have an appointment book database - tbls: Employees, Clients,
Appointments. When a client calls, the office needs to be able to see the
next available appointment. I have no problem showing what's already
scheduled! There are several employees who need to go in the
'appointment' tbl and block out their time to do paperwork, vacation, etc. so
that will have to be taken into consideration along with the appointments
they schedule. Outlook is not an option.

I thought about a blank calendar table with columns of Date, Time Slot (1/2
hr intervals), and Employee ID - but it'd be massive having a row for each
day, each 1/2 hr, and each employee.

At a loss,
Rachel

 

[MGFoster]

Been struggling with this...

I have an appointment book database - tbls: Employees, Clients,
Appointments. When a client calls, the office needs to be able to see the
next available appointment. I have no problem showing what's already
scheduled! There are several employees who need to go in the
'appointment' tbl and block out their time to do paperwork, vacation, etc. so
that will have to be taken into consideration along with the appointments
they schedule. Outlook is not an option.

I thought about a blank calendar table with columns of Date, Time Slot (1/2
hr intervals), and Employee ID - but it'd be massive having a row for each
day, each 1/2 hr, and each employee.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, that's how you'd do it, you'd create a calendar table without
the employee column. The calendar table would be the date and a 1/2 hr
increment per date. Like this:

Calendar
work_date DateTime - just put in the work dates that will hold appts.
start_time DateTime - this'd hold the time in 1/2 hr increments.

Set the primary key on both the work_date and start_time columns.

Sample data - you can write a VBA routine to fill the table.

work_date start_time
1/1/2006 8:00
1/1/2006 8:31
1/1/2006 9:00
....
12/31/2016 16:00
12/31/2016 16:31

Your Appointments table should have a work_date, start_time, and
employee_id columns to show a filled appt time slot.

Then to find an open time slot use a query like this:

PARAMETERS what_date Date, which_employee Long;
SELECT C.work_date, C.start_time
FROM Calendar As C LEFT JOIN Appointments As A ON C.work_date =
A.work_date AND C.start_time = A.start_time
WHERE C.work_date = what_date
AND A.start_time IS NULL
AND A.employee_id = which_employee

The LEFT JOIN of the Calendar table to the Appointments table and the
A.start_time IS NULL will produce an empty slot for the employee, unless
all slots are filled for the indicated date.

Don't work about the size of the Calendar table it won't be that large,
and using a VBA routine to fill it will only take seconds. Put in about
10 years worth of data & don't worry about it for 10 years ;-).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBPNYechKqOuFEgEQIRCwCgvG3Ro8hHIMzBqjkT0Y46NiUsf8AAoNw7
PRq/fthNLwvrRapfYIrK8J4k
=pvIg
-----END PGP SIGNATURE-----

 

[Guest]

Thank you! It'd made sense to do it that way...I guess my problem is not
knowing the VBA routine to set up the calendar table. I would like to set it
up from 10/01/06 to 12/31/2016 with half hour increments...that part eludes
me. I apologize for asking again, but would you happen to know the code for
it too?

Thank you again for your help!!

Rachel

 

[Guest]

Anyone? Anyone?

Thanks!
Rachel