Re: Schedule Database

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

So you have 2 stages in order to establish who is available to meet the
customer's needs:
a) Who is scheduled on at this time.
b) Who amongst those scheduled on at the time has no booking for any part of
that period.

a)
The person is scheduled on at the time if the worker's shift starts before
the appointment time starts, and ends after the appointment time ends.

b)
The person is not available if both:
- they have another appointment that starts before this one ends, and
- this one starts before the other appointment ends.

As explained previously, you can simplify (b) by recording each block of
time as a record on its own, which then makes it dead simple to check for
each of the blocks you need for this client's appointment.

One interesting sideline is that this logic will never find anyone available
for the job if the client wants someone from 11:30am to 6:00pm.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sammy" <sammy1971@xxxxxxxxxxx> wrote in message
news:eic8ZNPaGHA.596@xxxxxxxxxxxxxxxxxxxxxxx
Allen,
The nature of this business is like this. A customer calls requesting
service, the operator types in what the customer's best date and time and
do a search to find out the nearest appointment with the first available
tech and dispach the agent with an email with the customer's info.
I dont have a problem splitting the sku table to allow another table to
carry the work and invoices . my struggle is with the schedule. these
techs are scheduled based on 2 shifts "9:00 AM to 5:30 PM" and "12:00 PM
to 8:30 PM". Yes sometimes some of the calls would take more than the
allocated time or less time but I dont see anyway to accommodate this in
the database.
I hope this makes things clear and Thank you for your patience and desire
to help :)

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23$%23GPIEaGHA.3880@xxxxxxxxxxxxxxxxxxxxxxx
Sammy, are you storing appointments (assignment of service calls, what
you expect to happen), or work (what actually happened)?

I suspect you will find that what actually happens (and therefore is
billable) will not be the same as what is anticipated. Some calls you
expect to take 2 hours may take 4, and others may be done in 1.

The sku table suggests you are trying to generate invoices out of this.
Or perhaps you need tables for both appointments and for actual work, and
for the resultant invoices (with invoice detail)?

"sammy" <sammy1971@xxxxxxxxxxx> wrote in message
news:OK1ivaCaGHA.3532@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the reply Allen. This is what I have so far.
EmployeeTable
EmployeeID --->Primary Key
EmployeeFName
EmployeeLName
EmployeeLocation
EmployeePhone

Calls Table
CallID --> Primary Key
Sku ---> links to Sku table
Status
CallDate
Notes
AssignedTo --->link to employee table
CustomerID --->Link to customers table

CustomersTable
CusID --PrimaryKey
CustomerFName
CustomerLName
City
Phone

Schedule Table
SID -- > Primary Key
EmployeeID --> link to Employee
StartTime "DateTime field" 9:00 AM"
EndTime "DateTime field "5:30 PM"

Sku table
SkuID -->primary Key
Duration "number field" 2 = 1 hour, 3 = 1 hour and 30 minutes
Description
Price
taxable "yesNo filed"

after doing this I find myself drawing blank when it comes to the rest.
I know I need at least 2 more table for the schedule but I have no idea
where to start.
any help would be greatly appreciated

Sammy

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uU9P5W1ZGHA.4248@xxxxxxxxxxxxxxxxxxxxxxx
Sammy, there are 2 basic ways to build this kind of schedule database,
and the one you outline is one of those, so yes, your approach makes
good sense.

If you are certain that jobs will always be booked in 30-minute blocks,
the alternative is to create a record for each 30-minute block of the
appointment. With this approach, your appointments table would have 4
entries for service1, i.e. 10:00, 10:30, 11:00, and 11:30. The
advantages of this approach are that it makes it incredibly simple and
efficient to see when someone is free, find clashing appointments, and
print schecules. It is usually the preferred design where possible,
though it is impractical if you need the flexibilty of appointments
that do not work in specified blocks.

If you go with the block design, I suggest you store the time as an
integer representing the number of 30-minute blocks since midnight,
e.g. 0 = midnight, 1 = 12:30am, 2 = 1:00am. This avoids the problems
inherent in trying to match floating point values (which is how the
date/time field is stored.) You will also want a table that defines the
time blocks that make up a shift.

If you go with your more flexible design, you might be interested in
this article:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

"sammy" <sammy1971@xxxxxxxxxxx> wrote in message
news:e4XzaA1ZGHA.440@xxxxxxxxxxxxxxxxxxxxxxx
Hey Guys,
I am trying to create a schedule database for employee that do their
jobs based on a 30 minutes blocks. example
employeeA starts shift at 9:00 AM and Ends at 5:30 PM this employee
would have 2 service calls to do at client's location service1 from
10:00 AM to 12:00 PM and service2 from 1:30 to 3:30 PM.
I have employee table with all employee info like ID, Name, address
ServiceCalls table with callID employeeID ClientID TaskID and
tasksLength
ClientsTable with all client's data
EmployeeSchedule table with employeeID, ShiftStartTime, ShiftEndTime
DaysTable with all DayID, OnDutyOrOff,

What i am trying to do is build a query to pull up the first available
employee to perform a specific task. my question is does the database
design make any sense to anyone, if not whats the best way to to
achieve this goal?


.

Relevant Pages

  • Re: Schedule Database
    ... lets say I have a sku for Cleaning Vents cost is $100.00 durration 2 huours. ... before the appointment time starts, and ends after the appointment time ... my struggle is with the schedule. ... EmployeeID --> link to Employee ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Schedule Database
    ... Each timeslot is half an hour, so enter values a record for each number ... before the appointment time starts, and ends after the appointment time ... my struggle is with the schedule. ... EmployeeID --> link to Employee ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Schedule Database
    ... the appointment time starts, and ends after the appointment time ends. ... my struggle is with the schedule. ... EmployeeID --> link to Employee ... It is usually the preferred design where possible, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Schedule Database
    ... my struggle is with the schedule. ... EmployeeID --> link to Employee ... your appointments table would have 4 ... It is usually the preferred design where possible, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Schedule Database
    ... Sammy, are you storing appointments (assignment of service calls, what you ... Schedule Table ... EmployeeID --> link to Employee ... It is usually the preferred design where possible, ...
    (microsoft.public.access.tablesdbdesign)