Re: Schedule Database

Thanks Allen,
Now I understands what you meant when you tried to explain the better
approach :) that leaves me with one minor issue which is the sku length.
lets say I have a sku for Cleaning Vents cost is $100.00 durration 2 huours.
should that 2 hours be converted to 4 time slots?
thanks

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eJ9W2amaGHA.4040@xxxxxxxxxxxxxxxxxxxxxxx
TimeSlot table has just one field:
TimeSlotID Number pk (primary key)

Each timeslot is half an hour, so enter values a record for each number
between 18 to 40. 18 half-hours after midnight is 9am--your first
timeslot. 40 half-hours after midnight is 8pm--the start of your last
timeslot in a day. This defines the valid timeslots in your day.

ServiceCall table then has these fields:
CallID AutoNumber pk
EmployeeID Number fk to Employee.EmployeeID
ClientID Number fk to Client.ClientID
CallDate Date/Time just the date
TimeSlotID Number fk to TimeSlot.TimeSlotID

Now to book Employee 6 to Client 99 on 4/1/06 for 10am - noon, you enter
that info onto an unbound form and hit Go. The code adds these 4 records:
EmployeeID ClientID CallDate TimeSlotID
6 99 4/1/06 20
6 99 4/1/06 21
6 99 4/1/06 22
6 99 4/1/06 23

The advantage comes when you try to find out who is free for timeslots
22-25 in a day. You first create the query to ask who is scheduled on in
that period, and then add the subquery:
... AND NOT EXISTS (SELECT CallID FROM ServiceCall
WHERE (ServiceCall.EmployeeID = EmployeeSchedule.EmployeeID)
AND (ServiceCall.CallDate = DateValue(EmployeeSchedule.ShiftStartDate))
AND (TimeSlotID Between 22 And 25))

--
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:eefZ$9haGHA.508@xxxxxxxxxxxxxxxxxxxxxxx
Thanks again Allen,
I am afraid I am not following you when you say "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."
Can you provide me with an example of what you mean please? like the
structure of a table and some sample data.

Thanks

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:uEliBGQaGHA.4620@xxxxxxxxxxxxxxxxxxxxxxx
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.

"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
    ... 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
    ... 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
    ... 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: Personell Coverage in a timesheet
    ... > Tells the shift manager who works when on a daily basis using data> entered on the weekly schedule and auto schedules a 30 min lunch in the> middle of their shift with out overlapping 2 employee lunches in the> same time slot. ... If anyone needs a copy of these sheets to play> with let me know. ...
    (microsoft.public.excel.worksheet.functions)
  • Personell Coverage in a timesheet
    ... Daily Action Plan ... entered on the weekly schedule and auto schedules a 30 min lunch in the ... middle of their shift with out overlapping 2 employee lunches in the ...
    (microsoft.public.excel.worksheet.functions)
Quantcast