How to create a query that determines if an appointment is due

[austin3i62]

Greetings. I am a very very very novice Access user, making a database for a research group unit on my own. The layout of the db is pretty simple. Only two tables: Table 1 holds patient demographic information, and Table 2 holds dates for all appointments the patient has completed. They are linked by a unique four digit subject id (SID).

Our study conducts interviews with participants over a period of 15 months. There are 7 interview points (Baseline, 1, 3, 6, 9, 12 and 15 months). The date that the participant's next appointment is due is based on their baseline appointment, with alot of flexibility based on the participant's needs (they can come in a week early and up to 2 weeks late for the 1month, and the rest have a window of 1 month early to 1 month late. For example, someone due for their 6m on 6/1/2014 could complete it as early as 5/1/14 and as late as 7/1/14. To this end I created a query that generates the due dates for appointments based on their baseline date.

What I am trying to do is write a query that I can use to generate a report. This would tell me all the participant's who are due for an appointment in the current week or the current month. I've been able to do this for each appointment without a problem, however, I have been unsuccessful in getting Access to display all the participants due this month regardless of which appointment they are due for. In other words, I can get a query to tell me all the SIDs who have not yet completed their 1m and are still within range to complete it. I use a checkbox to indicate whether an interview has been completed for each interval, and if this checkbox has not been checked, they haven't completed their appointment. The query I made that works for this is simply to have 2 fields from the Follow-up table (SID and the 1m completed checkbox field) and determine if their due date for their 1m is between date()-7 and Date()+14. This works for all the appointments. My trouble is figuring out how to check if they are due for ANY appointments. In other words, I don't want to have 7 separate queries for each appointment date, I just want 1 query that will determine if any appointment is due.

Any help or tips would be greatly appreciated.

 

[austin3i62]

I might have made what I want to do more puzzling by trying to explain it too much. Let's say I have a participant that comes in for a baseline appt on 1/1/13. Their 1-month follow-up would be due 2/1/13, their 3-month on 4/1/13, the 6-month at 7/1/13, the 9-month at 10/1/13, the 12m at 1/1/14 and the 15m at 4/1/14. Is there a way that I can get Access to tell me who is due for an appointment in a certain time period (let's say the next month)?

 

[plog]

You need to create a query that has all the appointments for a participant. I see 2 ways to do this: 6 sub-queries (one for each follow up appointment) then a UNION query (http://www.techonthenet.com/sql/union.php) to bring that data together, or use a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product). Sounds like you understand how the 6 sub-queries will work, so let me explain the Cartesian Product.

First you would create a table to hold all the follow up time frames. It would look like this:

FollowUpMonths
MonthsOut
1
3
6
9
12
15

Next you would create a query to show a patient's baseline appointment. Then to that query bring in FollowUpMonths that I described above. Create a field using the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php) using the patients baseline date and adding the number of months from FollowUpMonths. They key is to not join the two data sources.

Run this and it will give you all your follow up appointments in one query.

 

[austin3i62]

The union query was what I was looking for! Worked perfectly for bringing up the results of the queries. Now I guess my only question is: Is there a way to change the output of a Yes/No field or Checkbox into a string so that I can simply determine WHICH follow-up appt is due? This is the SQL code for the union query you advised which worked beautifully:
SELECT [SID],[1MCompleted?] as AptDue,[1mRD] as DueDate
FROM [1mDueThisMonth]
UNION SELECT [SID],[3MCompleted?],[3mRD]
FROM [3mDueThisMonth];
UNION SELECT [SID],[6MCompleted?],[6mRD]
FROM [6mDueThisMonth];
UNION SELECT [SID],[9MCompleted?],[9mRD]
FROM [9mDueThisMonth];
UNION SELECT [SID],[12MCompleted?],[12mRD]
FROM [12mDueThisMonth];
UNION SELECT [SID],[15MCompleted?],[15mRD]
FROM [15mDueThisMonth];

The problem I'm now trying to solve is in the AptDue column obviously it's just saying yes or no. What I need is instead of saying simply yes or no, it outputs something like "1m Due" or "3m Due" etc. so that I know WHICH of the follow-ups is due on that date.

 

[plog]

Is there a way to change the output of a Yes/No field or Checkbox into a string

For that you want an Iif statement (http://www.techonthenet.com/access/functions/advanced/iif.php).

 

[austin3i62]

Worked beautifully! I had been scratching my head for over a week now with no clue how to proceed. I owe you a drink.