Hi again all,
I've been racking my brain trying to think how to get a query to work the way I want it to, but I can't quite get there.
What I want is to show all patients' future appointments, where they have any appointment within a given time frame.
For example, imagine a patient who has 2 appointments per month for the next 3 months, and the first of these appointments is next week. I want to write a query that will ask for date parameters, then check to see if any patients have an appointment within the defined time frame. Because our example patient has an appointment next week, if I set the parameters to next week, I want it to return all 6 future appointments for that patient. But, if a patient doesn't have an appointment within the time frame, I don't want it to return any records for that patient.
Is such a query even possible?
So far, I've only managed to write a query that returns all records within the given parameters, i.e. whoever runs the query is asked to define the parameters, and if the any patient has any appointment within those parameters it will show up. Any appointments outside of those parameters will not show up.
I've a picture of the SQL for my current query below, in case that's helpful.
Thanks in advance,
Adrian.