Is there a simple way to do the following? Assuming that the working
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?
On Thu, 10 Apr 2008 02:05:58 -0700 (PDT), 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.
>Is there a simple way to do the following? Assuming that the working
>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?
On Apr 11, 12:36=A0am, Tom van Stiphout
> 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. :-)
Wayne wrote:
> On Apr 11, 12:36 am, Tom van Stiphout
>
>
>>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. :-)
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
On Thu, 10 Apr 2008 12:36:56 -0700 (PDT), Wayne
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.
>On Apr 11, 12:36 am, Tom van Stiphout
>
>> 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. :-)
Hi Wayne
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"
news:d9d9cffb-a4e1-4556-9a79-38e106a81162@q10g2000prf.google groups.com...
On Apr 11, 12:36 am, Tom van Stiphout
> 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. :-)
Thanks for the replies. You've certainly given me some food for
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.
On Apr 11, 1:41=A0am, Wayne
> Thanks for the replies. =A0You've certainly given me some food for
> thought. =A0I'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.
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 =3D 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/9 ce7997df8677fca
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 >=3D 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
CDMAPoster@FortuneJames.com
On Fri, 11 Apr 2008 00:09:48 -0400, Tom van Stiphout
> On Thu, 10 Apr 2008 12:36:56 -0700 (PDT), Wayne
>
>
> 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.
lyle fairfield wrote:
> On Fri, 11 Apr 2008 00:09:48 -0400, Tom van Stiphout
>
>
>> On Thu, 10 Apr 2008 12:36:56 -0700 (PDT), Wayne
>>
>>
>> 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.
What's your opinion of Opera? My newsclient is getting old.
> What's your opinion of Opera? My newsclient is getting old.
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.
On Apr 11, 3:17=A0am, CDMAPos...@fortunejames.com wrote:
> On Apr 11, 1:41=A0am, Wayne
>
> > Thanks for the replies. =A0You've certainly given me some food for
> > thought. =A0I'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.
>
> 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 =3D 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/9 ce7997df8...
>
> 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 >=3D 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
> CDMAPos...@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) >=3D 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
CDMAPoster@FortuneJames.com
The approach in this thread looks wrong to me considering your example. Your
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"
news:04df1ed6-8bc9-4605-b837-d42e9ba01e49@n14g2000pri.google groups.com...
> Is there a simple way to do the following? Assuming that the working
> 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?
On Apr 11, 5:08 am, "lyle fairfield"
> On Fri, 11 Apr 2008 00:09:48 -0400, Tom van Stiphout
>
>
> > On Thu, 10 Apr 2008 12:36:56 -0700 (PDT), Wayne
> >
>
> > 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.
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