Appointment Due

[cheechootrain]

Hello,

So I'm trying to set up a query where it will tell me which patients will be due for a certain month in the future.

The purpose of this is to keep track of the neuromuscular patient population who will return on a regular but variable intervals to attend follow up clinics. The flow up interval is determined by their diagnosis. We have a large and increasing population of neuromuscular patients who are supposed to return for a follow up on a specified interval to attend several different clinics. For a variety of reasons, some of these patients are being lost to follow up and a better tracking system is needed.

There will be a patients table, an appointment table and each patient is assigned an interval (#months). The patients have some sort of neuromuscular disease (ALS, neuropathy).

So after they are seen once, they have to come back every 3 or so months (depending on their interval month) from then on. My question is, does anyone know how to set up a query where it can tell me which patients will be due in the future, based on their past appointments and interval?

For example, if a patient A is seen in April 22, 2013. If I ran a query for the month of March 2014, based on the interval for each patient, it will show only the patients who are due for that month. And if this patient A, who's interval is 4 months, lands on March 2014, it's name will show up. That is basically what it to achieve. I posted an earlier thread regarding this but we change that method now.



Thanks!!
Cheechoo

[June7]

Try:

1. Build a query that shows patient and last appointment date
SELECT PatientID, Max(AptDate) AS LastApt FROM Appointments GROUP BY PatientID;

2. Join that query to Patients table, calculate another field for yyyymmDUE: Year(LastApt) & Month(LastApt)+[Interval]

3. apply criteria to yyyymmDUE field:
=[input year] & [input month]

[cheechootrain]

Try:

1. Build a query that shows patient and last appointment date
SELECT PatientID, Max(AptDate) AS LastApt FROM Appointments GROUP BY PatientID;

2. Join that query to Patients table, calculate another field for yyyymmDUE: Year(LastApt) & Month(LastApt)+[Interval]

3. apply criteria to yyyymmDUE field:
=[input year] & [input month]

Thank you! Quick question, I am relatively new to MS Access. How do you join the query to the patients table? For step number 2? And when you say calculate another field, I use another query? I apologize for my naivety!

Thanks again!!

[June7]

Use the query designer. Pull query and table into the table window, create link on the PatientID fields (if it isn't already), drag fields to the field grid, create field with expression in a cell on the Fields row. Access Help has more guidance on using query designer and creating fields with expressions. http://office.microsoft.com/en-us/ac...005188023.aspx

[cheechootrain]

Use the query designer. Pull query and table into the table window, create link on the PatientID fields (if it isn't already), drag fields to the field grid, create field with expression in a cell on the Fields row. Access Help has more guidance on using query designer and creating fields with expressions. http://office.microsoft.com/en-us/ac...005188023.aspx

Thanks buddy!

[cheechootrain]

Thanks again for all your help! It actually worked. Now the problem I am having is trying to set the month to criteria. I have microsoft access 2010 and the =[2013] & [7] did not work in the criteria field of the query.

Also, as a side note, I changed the formula for yyyymmDUE to Due: Date Add("m",[Interval],[LastAppt]) and formated so it displayed results as Aug 2013. I tried using the >8/1/2013 and <8/31/2013 command and that worked. But I was wondering if their is a quicker way of doing this because we have to do it for 10 more dates and it takes a while typing it out. Here is the picture:




Thanks again for your help!!! If you're ever in Fresno, hit me up and I'll buy you drink!

Cheechoo

[June7]

Instead of typing static criteria, use a dynamic parameter. The input can be a popup (I don't do this) or reference to a control form. Examples:

>[Enter start date] And <[Enter end date]

or

>Forms!formname!textbox1 AND <Forms!formname!textbox2

[cheechootrain]

Instead of typing static criteria, use a dynamic parameter. The input can be a popup (I don't do this) or reference to a control form. Examples:

>[Enter start date] And <[Enter end date]

or

>Forms!formname!textbox1 AND <Forms!formname!textbox2

Thank you!!

[GHILLLII]

can you send me your finished product.. i believe i can tweak it for you