booking form help

[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

 

[chris davies]

To answer this question I must assume you know about primary and foreign keys because if you dont, you will have to look this up, this is a very important part of relational database theory. So here goes nothing...

To avoid double bookings you need an appointments table with a foreign key from the Time table - timeID, and a Date field set up in the appointments table, also you'll need a foreign key with whom the customer is booking the appointment. then set these three keys as the primary key for the table, it may be sensible to have TreatmentID (from the treatment table) as a foriegn key, but not part of the primary key. Dont use the customerID as part of the primary key because the customer changes every time per appointment and you will get duplicate values.

The important part here is to use TimeID (or whatever your Time table primary key is called), Date (must be set up as a field in the appointments table) and the primary key from a Staff table, all three set up as primary key for the appointment table this will eliminate double bookings. when you add a record and then use the save record button, you can alter the error message in the code for the save record to:

Err_cmdSave_Click:
MsgBox "This Appoinment Time Or Date Is Already Booked," & Chr(13) & _
" Please Choose Another Time Or Date"
Resume Exit_cmdSave_Click

the & Chr(13) &
just puts the error message on two lines instead of having a 6 inch wide alert box :O)
The msgbox can have any message of your preference.
Hope this helps
Chris

 

[chris davies]

forgot to add that treatment is not a unique identifier as many people may want the same treatment, this is why I say to use timeID,Date and StaffNo, setting these three as a concatenated key will ensure unique vlaues for each appointment.

to list specific appointments, set up a query based on the appointments table using the DateDiff function on the Date field, something like the following which will list all appointments over 6 months old (useful for a 6 monthly reminder check up sort of thing):

DateDiff("m",[Date],Now()) - which goes in the field section of the query and use the criteria of:
>5

I hope this helps
Chris

 

[bern494]

Reply

Hi
Thanks for your reply, and theirs me thinking I was at least an intermediate Access user, I don't think I have scratched the surface of it.

Having said that, I had a vague idea what primary and foreign keys were, but having spent a lot of time since your reply I cannot seem to grasp the use of them or even how to join them in a new table has you suggest.

My Patient table consists of data with a unique identifier as Primary key PatientID

I have a Treatment table with the primary key set as TreatmentID with below it the list of treatments

I made sure my Times table had a primary key named TimeID autonumber and another column with the relevant times i.e. 11:00am thru 19:00pm with 30min slots which gave me 17 entries.

I broke my date table down into significant days of the week i.e. tblMonday tblWendesday, tblThursday, tblFriday, and tblSaturday with a primary key named for the day of week with the relevant dates in that column.

I did not understand what you meant by staff taking bookings as only one person does it, so I presumed you meant the actual place where the booking was for, so I have a table named tblsalon which the primary key is Area of salon with the relevant salons listed below it.

So basically how do I make a new table using the primary keys or indeed how to transfer them into a new appointment booking table and how do I point a form to the relevant table.

I have in tables’ tblpatients, tbltimes, tblmonday, tblwendesday, tblthursday, tblfriday, tblsaturday, tblsalon, tbl treatment

Many thanks
Bern

 

[bern494]

Hi I think it may be prudent of me after trying for another couple of days to sort it, to send a copy of my database (named copy) to you or anybody else, to try and point me in the right direction.

I am trying work out a way to book new and existing customers an appointment at any of the 4 salons, Putney on a Mon, Weds and Sat, and Knightsbridge on Thursday with Canary Wharf on Thurs also but only afternoons, Liverpool St on Fridays. (NO TUESDAY APPOINTMENTS)
The times range from 11:00am thru 19:00pm in 30min slots.
The salon dates are the exact dates for the appropriate salon for about 4yrs

Hopeful of some feedback
Many thanks Bern
http://www.access-programmers.co.uk...=1&PHPSESSID=05a73c35547e4eedc648e20c51179fe1