CODEWITHSUNDEEP
sqldatabasems-accessms-access-2010
mjsey
mjsey

Reputation: 1978

Avoiding clashing appointments Access

I have three tables:

Employee(Id,name etc)
Appointment(Id,date,time,employee id, clientid etc)
Client(Id,name etc)

The Employee table has a 1 to many with the Appointment table as does the Client table.

However what I'm trying to achieve is to allow for the system to prevent duplicate or conflicting appointments but cant quite get my head around how to go about this. Would I need an additional table with say available time slots and some how link it all together? Or for example an employee availability table in addition?

Or could I achieve what I need with what I already have and just by manipulation of queries?

Many thanks

Upvotes: 1

Views: 1109

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

To prevent collisions, the logic here is quite simple:

A collision occurs when: 

RequestStartDate <= EndDate 
and 
RequestEndDate >= StartDate

The above is thus a rather simply query. If any collision occurs, the above will return records and you simply don't allow the booking.

The above of course can EASY be extended for time, and even a particular room.

Eg:

RequestStartTime <= EndTime 
and 
RequestEndTime >= StartTime 
And
RequestDate = BookingDate

And in fact in access since you can store a date + time column, then we are quite much back to the first example above (and as such a booking can span multiple days if you do this). And as noted, if this was for a particular room, then just add a room condition to the above.

Access 2010 does have table triggers and store procedures, but since you need a UI for the user, then code like this normally does the trick:

dim strWhere                 as string 
dim dtRequeestStartDate      as date 
dim dtRequestEndDate         as date 

dtRequestStartDate = inputbox("Enter start Date") 
dtRequestEndDate = inputbox("Enter end date") 

strWhere="#" & format(dtRequestStartDate,"mm/dd/yyyy") & "# <= EndDate" & _ 
     " and #" & format(dtRequestEndDate,"mm/dd/yyyy") & "#  >= StartDate" 


if dcount("*","tableBooking",strWhere) > 0 then 
    msgbox "sorry, you cant book 
...bla bla bla....

The above is just an example, and I am sure you would build a nice form that prompts the user for the booking dates.

So the above simple conditions above does return ANY collisions. And having written so many reservation systmes, I strongly recommend you do NOT create blank records ahead of time, but use the above correct logic and thus ONLY add records to the booking system and not have to write tons of code to create a bunch of blank records with time slots etc.

The above simply query will prevent collisions.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

I think an appointments table is going to be necessary. It will allow you to include only available slots and also to analyse employees workload and availability. The table would include 15 minute slots for each day for each employee. You may wish to add a further table for holidays / sick days / generally unavailable for a chunk of time.

EDIT

I had envisioned something on the lines of:

Timeslots:
EmployeeID ) Primary key
TimeSlot   ) 
JobID      - Foreign key
Status     ) And so forth
Notes      )

"I want an early appointment with E1"

SELECT TimeSlots.EmployeeID, TimeSlots.TimeSlot, TimeSlots.JobID
FROM TimeSlots
WHERE TimeSlots.EmployeeID=1
AND TimeSlots.TimeSlot Between #2/9/2012 9:0:0# And #2/9/2012 11:30:0#

"I want an appointment at 9:00am"

SELECT TimeSlots.EmployeeID, TimeSlots.TimeSlot, TimeSlots.JobID
FROM TimeSlots
WHERE TimeSlots.TimeSlot Between #2/9/2012 9:0:0# And #2/9/2012 9:30:0#

Upvotes: 1

Related Questions