which fields for an appointment?

[merlin777]

I'm designing my first database for the purpose of recording appointments for multiple therapists/patients and i'm looking at what fields to include in the appointment table.

When it comes to the time and length of the appointment, is it better to specify a start time and duration or a start and end time?

Also, should i have separate fields for year/day/hour/minute etc or treat this as one moment in time specified by all those things?

and, would it be better to have a database per year or one ongoing database?

the data will be used for displaying and printing timetables for patients and therapists and possibly summarizing the number of appoints for individuals and similar types of report. Appointments are multiples of 15 mins.

The general approach I'm taking is a table of appointments and tables for patients, therapists, types of therapy.

I'd value any opinions!

 

[Uncle Gizmo]

It sounds a lot like the sort of job I have had myself. There's an appointment system that works ok because Miss Jones does the appointments. However when Miss Jones isn't around, then the system goes all to pot.

So some bright spark comes up with the idea, we need a database!

You might have a case where Miss Jones is retiring and refuses, or makes it difficult for anyone to sit down with her and learn how to use her system.

In other words there is generally an office politic motive for having a database, and you the database developer are plonked right in the middle of it with very little authority / ability to manage the people and the situation.

Anyway not withstanding how you found yourself designing this database, you should design the database first as a manual process and then use that manual process as the guide for designing your database.

 

[merlin777]

I used to have someone like that working for me. Set the department back 5 years!

The team that will use it use outlook to record appointments and spend hours a week cross referencing and writing out timetables for patients so they are screaming for something to help - buy-in is a given.

As I'm inexperienced I'm looking for wisdom on how to specify the time in terms of fields and how to handle the duration.

 

[pbaldy]

I personally would store the start and end times rather than the start and duration. You're probably going to be searching a lot for overlaps and such, and having the end time will make that simpler. This may be of use at some point:

http://www.baldyweb.com/OverLap.htm

As to your other questions, I would store the date/time in a single field, not broken up into components. I would not have a database per year, just one with all data in it.

 

[merlin777]

thanks, Paul - that's very useful.

 

[spikepl]

Outlook recipients have a method called FreeBusy... THis means you can check availabiltiy by asking outlook instead of checking some internal Access table (that you'd still have to update with ALL appointment information, sick leave, absence, holidays, etc etc) . .perhaps you can link to the Outlook one holding appointments directly...(but this type of stuff is notoriously quite slow).

But before embarking on this I'd speend what it takes googling to convince myself that there is nothing out there that fits my needs which is what would necessitate bespoke development.

 

[pbaldy]

No problem, good luck with the project.

 

[merlin777]

Outlook recipients have a method called FreeBusy... THis means you can check availabiltiy by asking outlook instead of checking some internal Access table (that you'd still have to update with ALL appointment information, sick leave, absence, holidays, etc etc) . .perhaps you can link to the Outlook one holding appointments directly...(but this type of stuff is notoriously quite slow).

But before embarking on this I'd speend what it takes googling to convince myself that there is nothing out there that fits my needs which is what would necessitate bespoke development.

I've googled but its a no-budget favour and I can't find a free solution although I'm open to suggestions.

The main purpose of the appointments is to create patient timetables and therapists won't book appointments if they know they are on leave etc. They use the outlook shared calendar function so they can see what other appointments have been made.

 

[Reese]

I have a similar situation in that my database manages programs and events. So, while I've only been working with Access for 2 years, I'm happy to help you out.

When it comes to the time and length of the appointment, is it better to specify a start time and duration or a start and end time?

I agree with Paul, go with the start and end times. That's what I do in my system. If you need to calculate the number of hours/minutes for each appointment you can always use queries.

would it be better to have a database per year or one ongoing database?

Again, I agree with Paul and go with an ongoing database. When selecting and/or displaying data you can use select queries to narrow the results down to the current year. This way, if you need to look back or forward a year for whatever reason, it's just a matter of query criteria.

That Baldy Web suggestion looks pretty cool for a way to display overlap. When I first started my database I did some research on how to display the events booked in my database in a calendar format and didn't find an easy way to do that. Instead, we use Google Calendar in addition to Access.

Yes, it requires storing and editing the information in two locations, but in our case it works. Access holds all of the data, then we copy a select amount of that data into Google: date and start/end time, type of program (using a color key), client (in the title), location and travel time (in the location box) and who is facilitating the program (in the description). In our case it is especially useful because now the volunteers who sign up to facilitate these programs can see an up-to-date calendar, but only we can edit it.

I hope that helps.

 

[merlin777]

Thanks, Reese. I'll look into google calendar a bit more, I think. The problem I may have with it s security. The NHS probably has a policy against putting confidential patient information in the cloud.

My excel skill are fairly good so one of my options is to use excel to display appointments instead of google calendar.

I don't think displaying overlaps will be a problem as a therapist will put their appointments in their own calendar. However, I do need to check that a patient doesn't have clashing appointments, although this will be just a warning as sometimes the therapists see a patient in twos or threes so clashes need to be allowable. Displaying them on the patient's timetable should also be okay because the therapists have asked if appointments can be grouped into hourly slots according to their start time.

I'm heartened to learn you have only been programming access for a couple of years and you can manage this kind of thing - there's hope for me yet! I've learned virtually all my excel from the net, in particular the mr excel forum which is excellent and has helped me continually push my boundaries. I hope this forum is as good!

 

[Reese]

The NHS probably has a policy against putting confidential patient information in the cloud.

That's a good point, even if it's just a patient's name that could be an issue. Since the therapists already use shared Outlook calendar, perhaps that could be adapted to a different use? Or perhaps there is another 3rd party software that can share a calendar over an internal network.

I'm heartened to learn you have only been programming access for a couple of years and you can manage this kind of thing

Heh... it's been a bumpy road for me, that's for sure, and the more experienced people on this forum may have gotten rather annoyed at me for persistent and (to them) simple concepts that don't seem to penetrate my thick skull. But I certainly have learned a lot on here.

...So on a separate note, thanks to everyone who has put up to me!

 

[merlin777]

...So on a separate note, thanks to everyone who has put up to me!

...and hopefully will now put up with me, too!

 

[merlin777]

When my user enters the time for an appointment they only need to specify day and time as the year is implied and the minutes and seconds are irrelevant

So when they enter the data, how do you allow day and time but still satisfy the general . date format requirements without extra typing?

If you split the appointment into year/month/day/hours/mins/seconds I can see how that would be easy to just enter day and hour but I don't understand how it would work with one genal date/time field.

 

[Reese]

If you split the appointment into year/month/day/hours/mins/seconds I can see how that would be easy to just enter day and hour but I don't understand how it would work with one genal date/time field.

What I have done is I have 3 fields. A date field (formatted as short date with month/day/year), a start time field, and an end time field. So far as I know you can't include date and time into one field.

Of course I could be wrong, but even if you could I think it's easier to have the whole date in one field and then the time in a separate field.

 

[pbaldy]

A date/time field includes both, whether specified or not (it is stored as a Double). I've done it both ways, and I think it's generally easier to store them together. I present them to the user separately, but store them together.

 

[merlin777]

A date/time field includes both, whether specified or not (it is stored as a Double). I've done it both ways, and I think it's generally easier to store them together. I present them to the user separately, but store them together.

Is a date/time field a special field or is it still just a normal date field? Not sure how that works.

 

[pbaldy]

A normal date field.

 

[pbaldy]

More from MS:

Access stores the Date/Time data type as a double-precision, floating-point number up to 15 decimal places. The integer part of the double-precision number represents the date. The decimal portion represents the time.

Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). A date value of 0 represents December 30, 1899. Access stores dates before December 30, 1899 as negative numbers.

Valid time values range from .0 (00:00:00) to .99999 (23:59:59). The numeric value represents a fraction of one day. You can convert the numeric value to hours, to minutes, and to seconds by multiplying the numeric value by 24.