Results 1 to 10 of 10
  1. #1
    AdiDoyle is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Ireland
    Posts
    23

    How to Formulate Query

    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.


    Click image for larger version. 

Name:	SQL.jpg 
Views:	21 
Size:	148.1 KB 
ID:	29976

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Something like:

    SELECT * FROM Appointments WHERE [AptDate]>[start date] AND PatientID IN (SELECT PatientID FROM Appointments WHERE [AptDate] BETWEEN [start date] AND [end date]);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    AdiDoyle is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Ireland
    Posts
    23
    Hey June7,

    Thanks for your help. I see the logic in a statement like that. But I can't seem to adapt it to get it to work with my own SQL code. I think I've made my own code too complicated for my own good.

    The original code was this:

    SELECT [Dr_Title] & " " & [Dr_Name] & " " & [Dr_MiddleName] & " " & [Dr_Surname] & ", " & [Dr_Qualification] AS PageTop1, Doctor.Dr_Role AS PageTop2, Doctor.Dr_Address1, Doctor.Dr_Address2, Doctor.Dr_Address_City, Doctor.Dr_Address_County, "Phone No. " & [Dr_Phone] AS DocPhone, "Fax No. " & [Dr_Fax] AS DocFax, Date() AS CurDate, Patient.Patient_ID, [Patient_Title] & " " & [Patient_Name] & " " & [Patient_Surname] AS AdrName, Patient.Patient_Address1 AS Adr1, Patient.Patient_Address2 AS Adr2, Patient.Patient_City AS Adr3, Patient.Patient_County AS Adr4, [Patient_Title] & " " & [Patient_Surname] & "," AS GreetName, Count(Appointment.Appointment_Date) AS CountOfAppointment_Date, [Hsp_Dept] & " Dept., " & [Hsp_Name] & ", " & [Hsp_Place] & "." AS Hospital, IIf(IsNull(Appointment.Appointment_Date),Null,Week dayName(Weekday(Appointment.Appointment_Date-1))) AS DayName, Appointment.Appointment_Date AS ApptDate, Appointment.Appointment_Timeslot AS ApptTime, IIf(IsNull(Appointment.Appointment_Date),Null,Appo intment.Appointment_Eye) AS Eye, [Dr_Title] & " " & [Dr_Surname] AS RtrnDoc, IIf(IsNull(Appointment.Appointment_RevDate),Null,W eekdayName(Weekday(Appointment.Appointment_RevDate-1))) AS RevDayName, IIf(IsNull(Appointment.Appointment_RevDate),Null,[Appointment_RevDate] & " at " & [Appointment_RevTime] & ".") AS RevInfo
    FROM Patient INNER JOIN (Hospital INNER JOIN (Drug INNER JOIN (Doctor INNER JOIN Appointment ON Doctor.Dr_ID = Appointment.Dr_ID) ON Drug.Drug_ID = Appointment.Drug_ID) ON Hospital.Hsp_ID = Appointment.Hsp_ID) ON Patient.Patient_ID = Appointment.Patient_ID
    GROUP BY [Dr_Title] & " " & [Dr_Name] & " " & [Dr_MiddleName] & " " & [Dr_Surname] & ", " & [Dr_Qualification], Doctor.Dr_Role, Doctor.Dr_Address1, Doctor.Dr_Address2, Doctor.Dr_Address_City, Doctor.Dr_Address_County, "Phone No. " & [Dr_Phone], "Fax No. " & [Dr_Fax], Date(), Patient.Patient_ID, [Patient_Title] & " " & [Patient_Name] & " " & [Patient_Surname], Patient.Patient_Address1, Patient.Patient_Address2, Patient.Patient_City, Patient.Patient_County, [Patient_Title] & " " & [Patient_Surname] & ",", [Hsp_Dept] & " Dept., " & [Hsp_Name] & ", " & [Hsp_Place] & ".", Appointment.Appointment_Date, Appointment.Appointment_Timeslot, [Dr_Title] & " " & [Dr_Surname], [Appointment_RevDate] & " at " & [Appointment_RevTime] & ".", Appointment.Appointment_Eye, Appointment.Appointment_Date, Appointment.Appointment_Date, Appointment.Appointment_RevDate
    HAVING (((Appointment.Appointment_Date)>=[Enter Earliest Date in Period:] And (Appointment.Appointment_Date)<=[Enter Last Date in Period:])) OR (((Appointment.Appointment_RevDate)>=[Enter Earliest Date in Period:] And (Appointment.Appointment_RevDate)<=[Enter Last Date in Period:]))
    ORDER BY Patient.Patient_ID, Appointment.Appointment_Date;

    I've marked in red where I think I should be applying the changes you suggested. I've run into a lot of aggregate function related errors, among other things. The closest thing I've gotten to working was substituting the text in red for this:

    HAVING (((Appointment.Appointment_Date) BETWEEN [Enter Earliest Date in Period:] And [Enter Last Date in Period:])) OR (((Appointment.Appointment_RevDate) BETWEEN [Enter Earliest Date in Period:] And [Enter Last Date in Period:]))


    However, this doesn't do anything different to what I already have in the above query.

    How do I go about adding in the extra select statement which includes selecting patient IDs from all appointments which meet the criteria in red?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe do a query that does the filtering then reference that query in another query that does the JOINS and aggregation.

    Or don't do aggregation in query - build a report and use its Sorting & Grouping features with aggregate calcs.

    Why concatenate text like "Phone No. " and "Fax No. " - these can be labels in report.

    Advise not to use popup parameter input prompts - impossible to validate user input resulting in user frustration. Reference textboxes on form where you can enforce validation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    AdiDoyle is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Ireland
    Posts
    23
    Quote Originally Posted by June7 View Post
    Maybe do a query that does the filtering then reference that query in another query that does the JOINS and aggregation.

    Or don't do aggregation in query - build a report and use its Sorting & Grouping features with aggregate calcs.
    Either of these options would "run the clock" more than if I could just find a solution within this one query. I'm conscious that time is money and I don't want to run up the cost unnecessarily if I can help it.


    Why concatenate text like "Phone No. " and "Fax No. " - these can be labels in report.
    The "report" is being made out to exactly resemble a preexisting letter that was sent out to patients. As the number of patients grew it became necessary to create this database to create multiple patient letters at once, as creating them individually became impossible.

    Concatenating the words "Phone No." and "Fax No." with the numbers creates a more accurate representation of the original letter than using labels which would be static on the page, and run the risk of a large number extending into the margin.


    Advise not to use popup parameter input prompts - impossible to validate user input resulting in user frustration. Reference textboxes on form where you can enforce validation.
    Originally I had preset the time frame to the next calendar month, but it was requested that I change it. The end user wants the freedom to define these parameters.


    Thanks for taking a look and helping me out. I'll keep at it.

    Best,
    Adrian.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Separate query objects shouldn't be any slower than nesting them, however, go ahead and nest them after confirming they work as individuals.

    What do you mean by 'creating them individually' - Word docs?

    I don't really understand your reasoning for the concatenation unless you do not want to build a report object at all and want that literal text to display in query. Textbox horizontal position and width are fixed. Why would data extend into report margin if controls are properly sized and positioned?

    'Freedom to define these parameters' doesn't preclude inputting valid date structure. The validation would just be to assure that. Let them put in whatever date they want, but make sure it's really a date and no typos such as 8//7/2017 before executing query or report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    AdiDoyle is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Ireland
    Posts
    23
    Quote Originally Posted by June7 View Post
    What do you mean by 'creating them individually' - Word docs?
    Yup. The PA had been using a template in MS Word, and individually making out letters to each patient.


    I don't really understand your reasoning for the concatenation unless you do not want to build a report object at all and want that literal text to display in query. Textbox horizontal position and width are fixed. Why would data extend into report margin if controls are properly sized and positioned?
    I do what that text to display in the query. Depending who the doctor is, for example, what will be displayed is "Phone No. " and the relevant doctor's phone number. Same with fax. If I were using labels, I would have to space the text, "Phone No. ", apart from the number itself, and while it probably wouldn't make much of a difference, it seemed messier in my mind as I was making out the form. Both could achieve the same goal, admittedly, I just chose this way.

    'Freedom to define these parameters' doesn't preclude inputting valid date structure. The validation would just be to assure that. Let them put in whatever date they want, but make sure it's really a date and no typos such as 8//7/2017 before executing query or report.
    Point taken. That seems like finesse at this point, though. I need to get the query working first.

  8. #8
    AdiDoyle is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Ireland
    Posts
    23
    Ok, so from what I can tell, the SQL code below should get me what I want. However, when I run it I get this error message:


    Click image for larger version. 

Name:	Error.jpg 
Views:	11 
Size:	28.0 KB 
ID:	30006


    I've marked the code relevant to the error message in red below. I'd really appreciate if anybody could help me get this to work.


    SELECT [Dr_Title] & " " & [Dr_Name] & " " & [Dr_MiddleName] & " " & [Dr_Surname] & ", " & [Dr_Qualification] AS PageTop1, Doctor.Dr_Role AS PageTop2, Doctor.Dr_Address1, Doctor.Dr_Address2, Doctor.Dr_Address_City, Doctor.Dr_Address_County, "Phone No. " & [Dr_Phone] AS DocPhone, "Fax No. " & [Dr_Fax] AS DocFax, Date() AS CurDate, Patient.Patient_ID, [Patient_Title] & " " & [Patient_Name] & " " & [Patient_Surname] AS AdrName, Patient.Patient_Address1 AS Adr1, Patient.Patient_Address2 AS Adr2, Patient.Patient_City AS Adr3, Patient.Patient_County AS Adr4, "Dear " & [Patient_Title] & " " & [Patient_Surname] & "," AS GreetName, "We list hereunder, details of your next appointment(s) for injections in the " & [Hsp_Dept] & " Dept., " & [Hsp_Name] & ", " & [Hsp_Place] & "." AS ParaIntro, Count(Appointment.Appointment_Date) AS CountOfAppointment_Date, IIf(IsNull([Appointment].[Appointment_Date]),Null,WeekdayName(Weekday([Appointment].[Appointment_Date]-1))) AS DayName, Appointment.Appointment_Date AS ApptDate, Appointment.Appointment_Timeslot AS ApptTime, IIf(IsNull([Appointment].[Appointment_Date]),Null,[Appointment].[Appointment_Eye]) AS Eye, IIf(IsNull([Appointment].[Appointment_RevDate]),Null,[Dr_Title] & " " & [Dr_Surname] & " would like you to return to the private rooms, prior to your final injection in this sequence. Therefore, an appointment has been made for you on " & WeekdayName(Weekday([Appointment].[Appointment_RevDate]-1)) & ", " & [Appointment_RevDate] & " at " & [Appointment_RevTime] & ".") AS ParaReturn
    FROM Patient INNER JOIN (Hospital INNER JOIN (Drug INNER JOIN (Doctor INNER JOIN Appointment ON Doctor.Dr_ID = Appointment.Dr_ID) ON Drug.Drug_ID = Appointment.Drug_ID) ON Hospital.Hsp_ID = Appointment.Hsp_ID) ON Patient.Patient_ID = Appointment.Patient_ID
    GROUP BY [Dr_Title] & " " & [Dr_Name] & " " & [Dr_MiddleName] & " " & [Dr_Surname] & ", " & [Dr_Qualification], Doctor.Dr_Role, Doctor.Dr_Address1, Doctor.Dr_Address2, Doctor.Dr_Address_City, Doctor.Dr_Address_County, "Phone No. " & [Dr_Phone], "Fax No. " & [Dr_Fax], Date(), Patient.Patient_ID, [Patient_Title] & " " & [Patient_Name] & " " & [Patient_Surname], Patient.Patient_Address1, Patient.Patient_Address2, Patient.Patient_City, Patient.Patient_County, "Dear " & [Patient_Title] & " " & [Patient_Surname] & ",", "We list hereunder, details of your next appointment(s) for injections in the " & [Hsp_Dept] & " Dept., " & [Hsp_Name] & ", " & [Hsp_Place] & ".", Appointment.Appointment_Date, Appointment.Appointment_Timeslot, IIf(IsNull([Appointment].[Appointment_RevDate]),Null,[Dr_Title] & " " & [Dr_Surname] & " would like you to return to the private rooms, prior to your final injection in this sequence. Therefore, an appointment has been made for you on " & WeekdayName(Weekday([Appointment].[Appointment_RevDate]-1)) & ", " & [Appointment_RevDate] & " at " & [Appointment_RevTime] & "."), Appointment.Appointment_RevDate
    HAVING (((Patient.Patient_ID) In (SELECT Appointment.Patient_ID
    FROM Appointment
    WHERE Appointment.Appointment_Date BETWEEN [Enter Earliest Date in Period:] AND [Enter Latest Date in Period:])) AND ((Appointment.Appointment_Date)>=Date())) OR (((Patient.Patient_ID) In (SELECT Appointment.Patient_ID
    FROM Appointment
    WHERE Appointment.Appointment_RevDate BETWEEN [Enter Earliest Date in Period:] AND [Enter Latest Date in Period:])) AND ((Appointment.Appointment_RevDate)>=Date()))
    ORDER BY Patient.Patient_ID, Appointment.Appointment_Date;

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Include that expression in the GROUP BY clause as well.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    AdiDoyle is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Ireland
    Posts
    23
    Quote Originally Posted by June7 View Post
    Include that expression in the GROUP BY clause as well.
    I'm some klutz for missing that. Thank you so much.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums