Appointment Generator

[cheechootrain]

Hello all!

Thank you for your time and help. I currently trying to develop a patient database for a physician and I'm having trouble developing a formula or algorithm. Basically, this is what we're trying to do.

We'll enter a patient and a few information about that patient. Then we would assign an appointment date. From there, we want the system (whether through queries or simple table formulas) to calculate the next appointment based on the interval number for the patient. For example, if we assigned an appointment for March 23, 2013 to a patient, then put the interval number as "3", it would automatically assign to the patient the month June 2013, and then September 2013, and so on. It basically will assign that patient each third month based off the last month until that patient dies or until we stop it. In addition, if a patient misses an appointment, I want the capability to change the interval or the next appointment date. So as time passes, it would save the past appointments but leave the future appointments open to change.

I can use the simple DateAdd function to simply add 3 months to the appointment, but that requires attention and time everyday. We want something where we input two values and then the algorithm or formula gives us a continuous or infinite list of appointments.



Thanks for all your help!!!


Sincerely,

cheechoo

[rpeare]

In terms of a database you do not want to create an endless number of appointments without knowing an end point, you'd just be creating a bunch of appointments that may or may not ever be met. You should really only be generating one appointment (or possibly 2) in advance. Also, what happens if someone is due for an appointment in June but they can't make it until july. I'm assuming you can overwrite the month it's just giving you a 'starting point' from which you can pick a date.

As far as missing appointments goes, you will still want to keep that appointment record, you just want to have some sort of marker (or deduction based on other data in the record) that the patient showed up. You can still base the 'next' appointment on the date of a missed appointment.

[cheechootrain]

In terms of a database you do not want to create an endless number of appointments without knowing an end point, you'd just be creating a bunch of appointments that may or may not ever be met. You should really only be generating one appointment (or possibly 2) in advance. Also, what happens if someone is due for an appointment in June but they can't make it until july. I'm assuming you can overwrite the month it's just giving you a 'starting point' from which you can pick a date.

As far as missing appointments goes, you will still want to keep that appointment record, you just want to have some sort of marker (or deduction based on other data in the record) that the patient showed up. You can still base the 'next' appointment on the date of a missed appointment.

Thank you for your reply!

How about doing 100 appointments from a certain point? Is it possible? And if I do that, is it possible to query all patients for that specific month?

Thanks!

[rpeare]

I guess my very basic question would be WHY do you need to create that many appointments in advance. The only thing I can think of is possibly cancer radiation treatments but 100 still seems excessive but you're talking about 3 month spans. If you have an appointment every 3 months for 100 iterations that's 25 years in advance.

Let's break it down a bit.

How far in advance do you need to see your schedule projected?
How far in advance can you schedule a client and RELIABLY expect them to show up for their appointment?
Is your business open 7 days a week? because by automatically creating appointments you are not only going to have to generate a date, but a time as well and 3 months from today's date may fall on a weekend. If your business is not open on weekends you will still need human input to choose a time (unless you're not consulting your patients you're telling them when they're going to show up)

What I'm driving at here is creating appointments for any business, by necessity, involves human input because the client/patient/consumer chooses when to come in, not the business (at least any I've seen). As I said in my original post the 3 months in would be a place to START looking for the next appointment.

Perhaps more information on how your business actually runs might help.

[cheechootrain]

Well, it's not my business. I'm volunteering to help out a physician. But basically, the purpose of this database is to keep track of our patient population who return on regular, but variable, intervals to attend follow-up clinics. This depends on their diagnosis and how many times the patient has to be seen. So once the patient is seen at a clinic, they should be automatically scheduled for the subsequent clinics based on the interval (#months) that we can input. If a patient's interval needs to be change, we might then automatically expect their subsequent appointments to change as well.

Likewise, if a patient cannot attend a scheduled clinic they may elect to come to the next month outside of their scheduled rotation. We would then determine the subsequent appointments based on the originally assigned clinic going forward, the rescheduled clinic could now be define the basis for subsequent appoints.

I know how to add one appointment in the future, the problem is to have the query to continually generate as time passes. I know I can use the Date() function and such but I have trouble having it to continually generate. In addition, if we do find a formula or way, we also want to see how many patients will be present in the month so we can plan accordingly. We would want to run a query for the month of May and see who is due for that date. All we want is to predict the month if we can. It doesn't have to be complicated but a simple time series formula.

Maybe I should ask how do you have a query that produces multiple answers for example like a series.

How far in advance do you need to see your schedule projected?

Ideally, we want it to continue until the patient dies or leaves the program. However, predicting three or two into the future and having it automatically reset as time passes would also work.

How far in advance can you schedule a client and RELIABLY expect them to show up for their appointment?

Probably only one into the future.

Is your business open 7 days a week? because by automatically creating appointments you are not only going to have to generate a date, but a time as well and 3 months from today's date may fall on a weekend. If your business is not open on weekends you will still need human input to choose a time (unless you're not consulting your patients you're telling them when they're going to show up)

It's open during the week. We don't necessarily need to project a certain date, but we want to know the month. For example, we have the clinic only once a month. So we can always assign patients to them. But what we want is to predict how many will be due for that month.

For example,

We have Brad Pitt: Appointment 1/2/2013, Interval = 3 months

So Brad Pitt's appointments would show up as
March 2013
June 2013
September 2013
December 2013
March 2014
June 2014
September 2014
December 2014
and so forth.

So if we ran a query for all patients that are due in March of 2014, his name would pop up.

What I'm driving at here is creating appointments for any business, by necessity, involves human input because the client/patient/consumer chooses when to come in, not the business (at least any I've seen). As I said in my original post the 3 months in would be a place to START looking for the next appointment.

Perhaps more information on how your business actually runs might help.

Yeah, you're right. That was what I initially had. I had a Patients table and an appointment table. It required constant input of appointments in order to predict the next date. But my boss wants something where we just input the first date and it does not require human modification.

That's my dilemma. I'm not sure if it will work but I'm trying to develop it so patients do not get lost in the system.

If you have any advice or ways of accomplishing it, thank you for your time. I really appreciate it. I've been working on this project since January and the only thing I cam up with was a simple database using the DateAdd function.

[rpeare]

Ok based on your description this is one of the few times I would suggest using a temporary table.

Whenever you're doing predictions you do NOT want it to generate a record in your 'live' data. In other words, creating 'fake' appointments that may or may not ever be met is going to needlessly clog your system with data that you will eventually have to clean up (let's say you have a person that misses their appointment this month but comes in next, you would want their next 3 month appointment to come three months from NEXT month, not the appointment they missed so ALL of the 'fake' appointments you put in would be garbage, you'd have to delete them all and re-run your appointment generator).

With the use of a temp table you can cycle through all of your clients and project out when they would come in for their clinic follow ups for the next x many months (or years) based on their most recent kept appointment and their 'interval'.

You're going to need VBA code to do it regardless and I can show you how to do that if you're not comfortable with it, but you definitely DO NOT want to create your projections on your actual scheduling table, that's a big, big mistake in my opinion. Doing it on a temp table and purging/adding records is far less dangerous.

[cheechootrain]

Ok based on your description this is one of the few times I would suggest using a temporary table.

Whenever you're doing predictions you do NOT want it to generate a record in your 'live' data. In other words, creating 'fake' appointments that may or may not ever be met is going to needlessly clog your system with data that you will eventually have to clean up (let's say you have a person that misses their appointment this month but comes in next, you would want their next 3 month appointment to come three months from NEXT month, not the appointment they missed so ALL of the 'fake' appointments you put in would be garbage, you'd have to delete them all and re-run your appointment generator).

With the use of a temp table you can cycle through all of your clients and project out when they would come in for their clinic follow ups for the next x many months (or years) based on their most recent kept appointment and their 'interval'.

You're going to need VBA code to do it regardless and I can show you how to do that if you're not comfortable with it, but you definitely DO NOT want to create your projections on your actual scheduling table, that's a big, big mistake in my opinion. Doing it on a temp table and purging/adding records is far less dangerous.

That sounds perfect! I am unsure how to do VBA coding, will you be able to show me? Thank you so much for your help! I've been working on this project since January.

Here is what I've got so far:https://www.dropbox.com/s/ghrf8fvjbgchhyw/PD_16.accdb


Thanks again!!

[rpeare]

I can not read access 2010 databases, if you can convert it to something prior I'll take a look at what you already have otherwise I will have to give you a generic example.

[cheechootrain]

I can not read access 2010 databases, if you can convert it to something prior I'll take a look at what you already have otherwise I will have to give you a generic example.

Unfortunately, it won't let me save it as an earlier database so a generic example is fine. Sorry.

[rpeare]

Are you using calculated fields in your table or mutli value fields?

If you are, I would strongly suggest you change your database so it's not using them. They're both extremely bad ideas and I don't know why that functionality was added.

[rpeare]

Here's an example. Use the form to do your processing

cheechootrain.zip

[cheechootrain]

Are you using calculated fields in your table or mutli value fields?

If you are, I would strongly suggest you change your database so it's not using them. They're both extremely bad ideas and I don't know why that functionality was added.

Yeah, I was using calculated values in the table. I was going horizontally like:

Patient Name Interval 1stAppt 2nd apt 3rdApt
Brad 3 Jan 2013 April 2013 June 2013

and so on.

Here's an example. Use the form to do your processing

cheechootrain.zip

Thank you so much! I really like this format. I never thought about it. How did you calculate the temp table? Was it all manually? Thank you again for your help!

[rpeare]

I don't know what you mean by 'calculate the temp table'

Do not use multi value fields, you'll regret it they're extremely hard to parse and use.

break yourself from it now while it's not quite so painful

[cheechootrain]

I don't know what you mean by 'calculate the temp table'

Do not use multi value fields, you'll regret it they're extremely hard to parse and use.

break yourself from it now while it's not quite so painful

Yeah, that's what I initially proposed to my boss, but he wants to "revolutionize" or make some thing new. But thanks, your design gave me an idea of how to generate this. Or try atleast, lol

[rpeare]

Then I'd warn your boss, in the strongest possible terms, storing multi value and calculated values is incredibly poor practice and will lead (possibly) to corruption of your information over time. There's a reason every powerful database engine (SQL, ORACLE, etc) I know does not allow them.