Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2011
    Posts
    31

    Unanswered: Applying 'Between' criteria with 'Order By' to a query

    I have a query which performs a statistical test based on results of another query:

    SELECT AuditNames.Hospital, AuditNames.Room, AuditNames.Examination, AuditNames.[Patient Size], AuditNames.[Tube Voltage (kV)], AuditNames.[Tube Current (mAs)], AuditNames.[DAP (cGy cm2)], AuditNames.[Date of Birth], AuditNames.Gender, AuditNames.Age
    FROM AuditNames, PercentRmResults
    WHERE (((AuditNames.Room)=[forms]![RADForm]![cboRooms]) AND ((AuditNames.Examination)=[forms]![RADForm]![cboExam]) AND ((AuditNames.[Patient Size])=[forms]![RADForm]![cboSize]) AND ((AuditNames.[DAP (cGy cm2)]) Between ([PercentRmResults].[Average DAP (cGy cm2)]-(2*[PercentRmResults].[Std Dev of DAP])) And ([PercentRmResults].[Average DAP (cGy cm2)]+(2*[PercentRmResults].[Std Dev of DAP]))))ORDER BY AuditNames.Hospital, AuditNames.Room;

    This tests if a dose value falls within the specified range range from the results of the other query.

    Now i applied similar criteria to a different query where it should automatically perform the same test using the 'between' command in the criteria:

    SELECT DISTINCT AuditNames.Hospital, AuditNames.Room, AuditNames.Examination, AuditNames.[Patient Size], AuditNames.[Tube Voltage (kV)], AuditNames.[Tube Current (mAs)], AuditNames.[DAP (cGy cm2)], AuditNames.[Date of Birth], AuditNames.Gender, AuditNames.Age
    FROM PercentGenResults, AuditNames
    WHERE (((AuditNames.[Patient Size])=[forms]![RADForm]![cboGenSize]) AND ((AuditNames.[DAP (cGy cm2)]) Between ([PercentGenResults].[Average DAP (cGy cm2)]-(2*[PercentGenResults].[Std Dev of DAP])) And ([PercentGenResults].[Average DAP (cGy cm2)]+(2*[PercentGenResults].[Std Dev of DAP]))) AND ((AuditNames.Age)>=16))
    ORDER BY AuditNames.Hospital, AuditNames.Room, AuditNames.Examination;

    I get reasonable results...but i'm concerned that the comparison is based on all the values of the results query and not being applied for corresponding examination types.

    Any way i could apply 'order by' along with 'between' as criteria or something of the sort to test for each examination separately?

    The first query runs on selection of three criteria specific to a particular room, examination and patient size. The second on the other hand is more like a summary for all exams in all rooms for a chosen patient size...Even considered a union query that would perform the comparison for each examination and combine the results in the union query output, but i keep getting lost in the text of the sql screen.
    Last edited by clownyj; 07-28-11 at 07:02.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •