magicdds- said:
I wrote a database program for a Dr.'s office to keep track of patient's
personal information and billing information. I now need to make a patient
scheduler (like an appointment book) with multiple columns (one for each
operatory). Once I put a patient in an appointment spot in a particular
column, time, and day, I would like to record the appointment in the
patient's record in the database.
You don't need to reocrd it in "both" places. the very essance of a
relational database is that your booking calendar will be *driven* by the
data you save in each paiented "appointment list". In other words, you don'
t need to "copy" the data...it is the fact of making a booking that will
"add" the booking reocrd to the patientes file (as a related table).
Also, if the appointment needs to be
changed, I want to drag & drop the patient's name on the apointment
schedule
to the new appointment slot.
You likey not acieve the above but it likey just as user friendlty to open
up the clander, browse to the person appointment, and thenclick onit. You
can then have a option to "book" to antoher date, and that will re-launch
the clander...adn you browse to whatever date, open it, and browse to
whatever time slot. This is really just a fine as drag and drop (which
ms-access unfortantly does not support).
Can this scheduler program be written in Access. If so, where do I begin?
There are some sample full screen calendars here:
http://www.granite.ab.ca/access/calendars.htm
And, for booking desings (that are relatonal). You can find some here:
http://www.databaseanswers.com/data_models/index.htm
Also, you don't need to actualy create "slots", you simply only need to
store the start/end time..and *test* for collesons.
The trick in a booking system is to only store the start and end date of the
booking.
And, 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, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.
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 can't 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. Howver, what is nice here is that
the simple condistion above does return ANY collsion. If you use a time/date
field, the time portion also works as above.
So, you don' need to pre-fill the calendar with booking slots, you simply
book *into* the calendar with start/end date times.....
And, I even a few screen shots of a calendar I made in ms-access here:
http://www.kallal.ca/ridestutorialp/setdriver.htm