Help with a booking form

[bern494]

Hi
I have a database which uses a form to book appointments, new or existing customers are booked in this way, or not as this case may be.
I have designed the form with my three options to book someone in, that are a Date, a Time and the treatment.

My date is taken from tables containing the appropriate dates for any Monday, (No Tues) another table for Weds etc until Sat for the next 4 years
My Time is taken from one table containing appropriate times in the day.
My Treatment is taken from a table containing a list of treatments.

How can I book an appointment without duplicating dates and times?
How can I produce a list of these bookings either daily, or weekly to forward these details via Microsoft Outlook?
I hope I am not asking for too much as I have been trying to do this on my own now for a while and have searched the board.
I have got this far so I don't want give up now.
Many Thanks
Bern

 

[Andrew Fergus]

Hi Bern

Why do you have tables for the dates and times? If you wish to restrict the appointments to say weekdays during office hours then this can be done without creating separate tables for the dates and times. Also, are the bookings made for service providers, or just the one? (i.e. you)

The way to prevent duplication is to have a table of appointments with something like the following fields :

tblAppointments
Date (this is the table key, along with the time. i.e. a joint table key)
Time (this is the table key, along with the date i.e. a joint table key)
Client_id_link (linked to the client table, which contains all of the client details)
Treatment_id (linked to the treatments table, containing details about each treatment)
Duration (in minutes, you could exclude this field if every appointment was of the same duration e.g. 30 or 60 minutes)

Having a joint date & time key prevents duplicate bookings - you can't have 2 appointments at the same time on the same day with this table. If there are multiple service providers (i.e. the people whom the clients are making appointments to see) then you need to add the service provider id into the appointments table and also make that part of the joint primary key. Your appointments form would be based on this table.

I haven't answered the reporting side of your question - that would be better answered once your data structures have been finalised.

HTH, Andrew