Appointments with three types of roles

[Joso]

hi everyone, i sort of run into a dead end trying to figure how to structure this. I have three tables for the 3 roles, tblClients, tblDoctors, tblBusinessContacts, each with a Primary key and some unique information about each role. I'm not sure how i can track appointments to each of these roles, i know how to do it if it was one role. I want to be able to later view all appointments for that day with all 3 of these roles.

Also, for clients i have a multiselect listbox describing their reason for the appointment and somewhow would like to associate that to the appointments. Some info i would like about these appointments:

1. date and time
2. details of contact
3. date for next appointment

What would be my best bet?

Thanks!

Joso

 

[Fizzio]

In the most simple of versions, you simply need another table with Appointments in it. Your structure depends on how much interactivity there is between the 3 roles

eg

Does a Client meet with a Doctor? Can any of them meet with one (or more) of the other or do they meet with other people outside your Db structure.

This will dictate how you structure your (new) Appointments table

 

[The_Doc_Man]

Structurally, you have already given yourself a headache. Meetings aren't between roles. They are between people. People have roles. They can have different roles in different meetings, I would think.

I would do something like this...

tblPerson
fldPersID, autonumber, PK
fldFirstN, text
fldMidN, text
fldLastN, text
fldAddrLine1, text
fldAddrLine2, text
etc.

tblDoctors
fldPersID, Long, FK
info specific to doctors.

tblClients
fldPersID, Long, FK
info specific to clients

tblContacts
fldPersID, Long, FK
info specific to business contacts

tblMeetings
fldMtgID, Autonumber, PK
fldMtgTime, Date/Time
fldMtgEnd, Date/Time
fldMtgLoc, text
etc.

tblAttendees
fldMtgID, Long, FK
fldPersID, Long, FK
fldRSVP, integer, encoded: Accepted, Tentative, Declined, Unreachable, Purposely scheduled extraction of wisdom teeth or adult circumcision to avoid this meeting, Accepted only if it's a cold day in Hell, etc.

Then you have one-to-one relationships between the tblPerson table and the three person-type tables. Note that this allows a Doctor to also be a Business Contact and a Client simultaneously.

You have a many-to-one relationship between Attendees and Persons. You have a many-to-one relationship between Attendees and Meetings.

To schedule a meeting, you need a time, a place, a length, and a list of attendees. This structure allows you to schedule as far ahead as you wish because each meeting exists regardless of the attendees you want to invite.

Now, the trick will be how you want to report on each person's roles when you look at the Attendee list. Or WHETHER YOU NEED TO!

How do you manage a report of who is at a meeting and show their role-specific stuff? Well, there are ways and then there are ways. A three-legged UNION query might work. You would have to define special generic names for the disparate fields from the role-related tables so that a single report would do the trick. Read up on UNION queries before you try this.

 

[Fizzio]

Good point Doc, thanks for the correction.

 

[Joso]

Thanks doc for pointing that out, i originally had a person table, but a friend suggested the other way. luckily i still have a copy of the old one lying around.

I just wanted to note so that we are the same pace here. The three types of roles are all like customers. A nutritionist is interacting with these 3 roles. These roles don't relate to eachother as in don't see eachother. The nutritionist will have appointments with the roles at different times.

I also have a multi select list (reasons for appointment such as new diet plan, diabetes...etc) for clients only, how would i associate that to the meeting table and for only clients? Maybe i can add something to the list called "doctor/business interaction" , so they can select that from the list whenever they are meeting with a doctor or busines contact?

what do you mean by this "you would have to define special generic names for the disparate fields from the role-related tables?

I might need to create something for the staff to associate them to who/people they are seeing. Could i create a staff table with their information and create a one to many relation ship to the meeting table? How would i go about creating the form for the appoinments?

thanks doc, you are like a surgeon with this stuff.

Take care

Joso

 

[Joso]

Hi, i am trying to figure out the best way to build a form based on doc man's suggestions. ANy ideas as to building the form/sub form with tblPerson and tblClients? the same goes for tblPerson and tblDoctors and business contacts.

Thanks!

Joso

 

[The_Doc_Man]

The "special names" comment relates to the way a UNION query works. You can use a UNION query to pull together data from multiple tables (or multiple JOIN queries), but you must pick a name for each field when the selected fields differ from one original table/query to another.

So

SELECT PersID, "DOCTOR" as Role, Specialty, ... FROM tblPerson LEFT INNER JOIN tblDoctors ON PersID etc.
UNION
SELECT PersID, "CLIENT" as Role, "" as Specialty, ... FROM tblPerson LEFT INNER JOIN tblClient on PersID etc.
UNION
SELECT PersID, "CONTACT" as Role, SupplierOf as Specialty, ... FROM ...

In other words, maybe one table calls their field of endeavor a Specialty, but another says they supply something, and the third doesn't have anything that corresponds to that concept. So you pick a name, say Specialty, and use the As clause to rename the closest matches from the other tables. You can supply constants (as shown for Role) or blanks for some cases (as shown for Client Specialty).

Or, as you point out, the Client has a reason for the visit, which might be that they are diabetic, on a new diet, or have celiac sprue and therefore need a low-gluten diet or something such as that. You could define the "Reason" field by using "..., Reason As Specialty, ..."

The final UNION query will have fields PersID, Role, Specialty, and anything else you included in the SELECT part. Even though the name Specialty on the third column doesn't exist in every table, it exists in the UNION query.

This is how you re-merge tables split by disparities in the descriptions of the members of those tables.

To build data entry forms that relate to doctors, clients, and contacts, you will potentially need three forms (or one VERY INTELLIGENT FORM - I vote for three forms...) and three queries that JOIN the person table to the details table. Driven with the details table as the primary table. I.e. define it so that the doctor's table looks at the doctor's detail table JOINED with the person table. You can do it the other way around but that would lead to trouble if you ever DID have a person who could play two roles. Besides, I would be there is no reason why a Contact couldn't also be a Client. After all, they would surely know of your business and would know whether you were trustworthy.

Of course, you would have to be sure that you separated all Doctor data away from all Person data so that the two tables would be relatively "pure" in terms of what was stored where.

 

[Joso]

Thanks Doc Man for your help.

Right now i'm trying to figure out in the appointments/meetings table with a start time and end time of the appointment on how to make it so that someone can't select a time anywhere between the start and end time. For example, let's say i made an appointment for 12/25/2004 for 10:00am until 11:00am, i want to forbid the user from selecting a start time anywhere between 10 and 11 am. Or even forbid someone to select an end time between 10 and 11am, so if they enetered another appointment for that date at 9am, they can only allow up to 10am since another appointment starts at 10am. I hope i make sense.

let me know if that's possible

Thanks again

Joso

 

[The_Doc_Man]

Now you run into the other problem with Access: Times.

Access does not automagically populate a table with possible times at which you would accept appointments. So you either have to build such a table yourself as a query driver, or you have to write VBA code and some serious intelligence in the form.

The way to determine whether a proposed new appointment overlaps an existing appointment involves at most a couple of IF statements if you used VBA. It is uglier (but still possible) with a raw query or two.

Basically, two appoints A and B, with start times Ast and Bst, end times Aen and Ben, overlap in either of the following cases:

Ast <= Bst and Aen > Bst
Ast < Ben and Aen >= Ben

Because of the <= or >= cases, this is not a simple BETWEEN ... AND ... case. It is single-ended inclusive, not double-ended. In English, two appointments overlap if the new appointment brackets either end of the old appointment.

Now, how do you make this work?

You need an appointment table that stores a start time and end time for each appointment. Your business rules would cause the times to be filtered so no one can schedule an appointment for, say, 9:18 AM - here, you would force a normalization on the times to the nearest hour or half-hour or whatever your business rules allow. Once the appointment is made, it is in an appointment table.

Conflict resolution occurs when you write a query with two criteria. Might need it to be a parameter query, which is described in the Help Files. What you want is EITHER a list of conflicting appointments or a COUNT of conflicting appointments (summation query). The start and end times of the proposed NEW query would be the parameters of the query.

This design presumes that you store start and end times rather than start time and duration. You could do it with start/duration but you would have to compute the end time anyway to do this bracketing. The question then becomes 'which way involves fewer computations?' To me, it is the start-time/end-time case because every conflict search has to compute the end-time of every appointment in the start/duration case. But if you develop enough appointment records, that becomes a burden.

 

[Joso]

I created a meetings table with the 2 fields starttime and endtime, both of them to a look up table too enforce rules of time. In the lookup i have a list of times 8:00am, 8:15am, 8:30am....evry 15 minutes so they can't enter an odd time like 8:23am. or even accidentaly type it in.

Now, i am still confused on what to do from here or what event i have to do the if statements and so forth. If anyone has an example that would be great.

Thanks

joso