Displaying Unused Time Slots
Wayne
day is 6am to 6pm and appointments are booked throughout the day for
example: 8:00am - 9:30am, 10:00am - 1:00pm, 2:00pm - 3:00pm.
How would I go about displaying the unused time slots i.e. 6:00am -
8:00am, 9:30am - 10:am, 1:00pm - 2:00pm and 3:00pm - 6:00pm?
Tom van Stiphout
<cqdi...@volcanomail.com> wrote:
You could use Outlook's calendar to store the appointments. A familiar
interface with many features built-in, and it's easy to see which
slots are still open.
-Tom.
Wayne
> You could use Outlook's calendar to store the appointments. A familiar
> interface with many features built-in, and it's easy to see which
> slots are still open.
>
> -Tom.
Thanks Tom, but I was hoping there is some way that I can do this in
Access. The appointments and their times are stored in an Access
table. I need to query that table, maybe against another reference
table with the 6:00am to 6:00pm period broken down into 15 minute
slots?, and then display the time periods that aren't taken by
appointments. Ideally the result would be the output of a query that
would show the unused time periods. Maybe I'm asking the impossible,
I don't know. :-)
Salad
You asked if there were a simple way. Short answer...no.
If you can program, you could select the records for today sorted by
time then loop thru the recordset and either store the results to an
array, a table, a UDF or listbox or combo box or some method to display
the results.
How do you want to present the results? You have timeslots taken and
timeslots available. First thing in the morning you have 0 taken and 1
slot open...6a-6p.
The only thing that needs to be stored are timeslots taken. You could
present open slots as a listbox and a text box/record to take in a new
time slot.
Graduation Day
http://www.youtube.com/watch?v=vJBH8uu5qIM
Tom van Stiphout
<cqdi...@volcanomail.com> wrote:
I can't think of the exact SQL right now, but I have a feeling one
should be able to come up with it: use a cartesian product query
between the table with timeslots and the appointments table to get all
possible timeslots for all days. Then do a "WHERE NOT IN (select ..."
query to find those slots that are not already taken.
Salad may be right and a procedural approach (recordsets, vba) may
yield better or quicker results.
I still like Outlook for this problem. You never gave a good argument
for why not to use it. Note that there are several Outlook lookalike
3rd party activex controls you could use as well.
-Tom.
KC-Mass
Use either a table with three fields or an array with three elements.
The fields/elements are StartTime, EndTime and Taken. Standard values are
assigned to these based on your 12 hour day and fifteen minute increments.
(thus when you initialize the table or array the first records values are
StartTime = 6:00, Endtime = 6:15, taken = No.). There are a total of 48
records/elements.
Let's assume you have the start time that someone wants and the ending time
that same someone wants on
a form.
You run a query that references the form values and selects records where
the records have a starttime equal to the start time someone wants or that
has an endtime equal to the end time that someone wants. The query will
pick up two records. That gives you the bookends that cover the requested
period of time. Then reference that query with an update query that marks
every segment between the book ends as taken.
When you want to show the untaken periods you could simple select all for
Taken = No or using the idea of bookends again you could get the start time
and end time of compound periods.
Regards
Kevin
"Wayne" <cqdi...@volcanomail.com> wrote in message
news:d9d9cffb-a4e1-4556...@q10g2000prf.googlegroups.com...
Wayne
thought. I'll have a play with your suggestion KC-Mass and I'll also
take a look to see if there's a third party control that might provide
a solution.
CDMAP...@fortunejames.com
Here's a start toward a solution:
tblAppointmentDays
ADID AutoNumber
DayStart Date/Time
DayEnd Date/Time
ADID DayStart DayEnd
1 4/14/08 6:00 AM 4/14/08 6:00 PM
2 4/15/08 6:00 AM 4/15/08 6:00 PM
3 4/16/08 6:00 AM 4/16/08 6:00 PM
...
tblAppointments
AID AutoNumber
AppointmentStart Date/Time
AppointmentEnd Date/Time
AID AppointmentStart AppointmentEnd
1 4/14/08 9:30 AM 4/14/08 9:45 AM
2 4/14/08 11:00 AM 4/14/08 11:30 AM
3 4/14/08 12:30 PM 4/14/08 12:45 PM
4 4/15/08 9:30 AM 4/15/08 9:45 AM
5 4/15/08 11:00 AM 4/15/08 11:30 AM
6 4/15/08 12:30 PM 4/15/08 12:45 PM
7 4/16/08 7:00 AM 4/16/08 11:00 AM
tblIntegers
ID AutoNumber
theInt Long
ID theInt
1 1
2 2
3 3
4 4
...
96 96
I.e., there are 4 * 24 = 96 possible 15 minute intervals in a day.
qryAppointmentSlots:
SELECT DateAdd("n", 15 * (theInt - 1), DayStart) As AppointmentSlot
FROM tblAppointmentDays, tblIntegers WHERE DateAdd("n", 15 * (theInt -
1), DayStart) < DayEnd ORDER BY DayStart, theInt;
!qryAppointmentSlots:
AppointmentSlot
4/14/2008 6:00:00 AM
4/14/2008 6:15:00 AM
...
4/14/2008 5:45:00 PM
4/15/2008 6:00:00 AM
...
4/15/2008 5:45:00 PM
4/16/2008 6:00:00 AM
...
4/16/2008 5:45:00 PM
qryFilledSlots:
SELECT AppointmentSlot FROM qryAppointmentSlots, tblAppointments WHERE
TimeIntersection(AppointmentSlot, DateAdd("n", 15, AppointmentSlot),
AppointmentStart, AppointmentEnd) > 0 ORDER BY AppointmentSlot;
AppointmentSlot
4/14/2008 9:30:00 AM
4/14/2008 11:00:00 AM
4/14/2008 11:15:00 AM
4/14/2008 12:30:00 PM
4/15/2008 9:30:00 AM
...
4/15/2008 12:30:00 PM
4/16/2008 7:00:00 AM
...
4/16/2008 10:45:00 AM
where the TimeIntersection() function is from:
http://groups.google.com/group/microsoft.public.access/msg/9ce7997df8677fca
Since the number of hours of intersection is not required and noting
that an appointment cannot be shorter than 15 minutes, I believe the
WHERE condition can be replaced as follows:
qryFilledSlots:
SELECT AppointmentSlot FROM qryAppointmentSlots, tblAppointments WHERE
AppointmentSlot >= AppointmentStart And AppointmentSlot <
AppointmentEnd ORDER BY AppointmentSlot;
!qryFilledSlots:
4/14/2008 9:30:00 AM
4/14/2008 11:00:00 AM
4/14/2008 11:15:00 AM
4/14/2008 12:30:00 PM
...
It would be bad form to use:
qryUnfilledSlots:
SELECT AppointmentSlot FROM qryAppointmentSlots WHERE AppointmentSlot
NOT IN (SELECT AppointmentSlot FROM qryFilledSlots) ORDER BY
AppointmentSlot;
!qryUnfilledSlots:
4/14/2008 6:00:00 AM
4/14/2008 6:15:00 AM
...
4/14/2008 9:15:00 AM
4/14/2008 9:45:00 AM
4/14/2008 10:00:00 AM
4/14/2008 10:15:00 AM
4/14/2008 10:30:00 AM
4/14/2008 10:45:00 AM
4/14/2008 11:30:00 AM
...
A standard unmatched query on qryFilledSlots and qryAppointmentSlots
doesn't like the dependencies so I'll have to think about the final
piece to the puzzle some more.
James A. Fortune
CDMAP...@FortuneJames.com
lyle fairfield
<no.spam...@cox.net> wrote:
> On Thu, 10 Apr 2008 12:36:56 -0700 (PDT), Wayne
> <cqdi...@volcanomail.com> wrote:
>
> I can't think of the exact SQL right now, but I have a feeling one
> should be able to come up with it: use a cartesian product query
> between the table with timeslots and the appointments table to get all
> possible timeslots for all days. Then do a "WHERE NOT IN (select ..."
> query to find those slots that are not already taken.
Do you think a NOT IN operator on a sub-query might be very slow?
OT: I'm experimenting with News Clients; this is Opera's.
Salad
What's your opinion of Opera? My newsclient is getting old.
lyle fairfield
It does many things very well:
- it's very fast
- it uses MDI (Multiple Document Interface while most (all?) other
browsers use Single Document Interface); this means you can tile your
windows within Opera; I find this helpful for jobs like updating accounts
(I think Firefox is ready to offer this in its next manifestation)
It doesn't do some things well
- it caches very aggressively ... go back to updating accounts ... you
can update them ... trying to get Opera to show the updates before sometime
next week is a struggle
- it has a beautiful integrated mail client ... but the client
composes in text only.
Probably this is too far off topic and I'll stop after this post.
Apologies.
CDMAP...@fortunejames.com
I decided to try filtering out the filled slots while getting the list
of possible slots:
qryUnfilledSlots:
SELECT DateAdd("n", 15 * (theInt - 1), DayStart) AS AppointmentSlot
FROM tblAppointmentDays, tblIntegers WHERE DateAdd("n", 15 * (theInt -
1), DayStart) < DayEnd AND NOT EXISTS (SELECT * FROM tblAppointments
WHERE DateAdd("n", 15 * (theInt - 1), DayStart) >= AppointmentStart
AND DateAdd("n", 15 * (theInt - 1), DayStart) < AppointmentEnd) ORDER
BY DayStart, theInt;
That seems to run quickly, but I'm not positive that it will perform
well when tblAppointments gets large.
James A. Fortune
CDMAP...@FortuneJames.com
Steve
example shows you have random time periods between 6AM and 6 PM for
appointments. Appointment 1 is 1-1/2 hour, Appointment 2 is 3 hours and
Appointment 3 is 1 hour. It is only by coincidence (or common practice)
that the appointment start and end times fall on what looks like the quarter
hour. Actually your example shows appointment start and end times fall on
the half hour. If the above is true, unused time slots are:
6 AM to 6 PM if no Appt 1
Appt 1 end time to Appt 2 start time or 6 PM if no Appt 2
Appt 2 end time to Appt 3 start time or 6 PM if no Appt 3
Appt 3 end time to Appt 4 start time or 6 PM if no Appt 4
Steve
"Wayne" <cqdi...@volcanomail.com> wrote in message
news:04df1ed6-8bc9-4605...@n14g2000pri.googlegroups.com...
Bruce
> On Fri, 11 Apr 2008 00:09:48 -0400, Tom van Stiphout
>
Sometimes yes, sometimes no. Generally I do avoid NOT IN but
sometimes I'm surprised to find it works rather quickly. If you find
NOT IN is slow, try rewriting as an outer join or vice versa and
figure out which works best.
Bruce