GROUP BY syntax

  • Thread starter Thomas J. Brooks, Jr.
  • Start date
T

Thomas J. Brooks, Jr.

Hi there...

I'm having an issue with data I'm selecting from an Access table using
ASP-based SQL.

Background: I have a form where a user enters a value (called text1 where
I'm entering in 2 as the value) and clicks a submit button, and it passes a
set of parameters, including text1, to an SQL page that executes my script.
I have the following query that uses 3 tables: labs, encounters, and
patients:

myquery="SELECT labs.clinic_code, labs.patient_id, labs.type_test,
MAX(labs.lab_date) AS 'maxlabdate', patients.last_name, patients.first_name,
patients.phone, labs.result, encounters.hlth_dm_1, encounters.hlth_dm_2,
encounters.hlth_dm_other FROM (([labs] INNER JOIN patients ON
labs.clinic_code = patients.clinic_code and labs.patient_id =
patients.patient_id) INNER JOIN encounters ON labs.clinic_code =
encounters.clinic_code and labs.patient_id = encounters.patient_id) WHERE
labs.clinic_code='"&user&"' and labs.result > "&text1&" GROUP BY
labs.clinic_code, labs.patient_id, labs.type_test, patients.last_name,
patients.first_name, encounters.hlth_dm_1, encounters.hlth_dm_2,
encounters.hlth_dm_other, patients.phone, labs.result HAVING
max(labs.lab_date) <= date() and trim(ucase(labs.type_test))='LDL' and
(encounters.hlth_dm_1=True or encounters.hlth_dm_2=True or
encounters.hlth_dm_other=True) ORDER by patients.last_name,
patients.first_name"

If I have user ABC (another parameter that I've passed) and patient 223344,
and they had an LDL test on 3/1/2005 with a result of 4.7 and another test
on 4/1/2005 with a result of 3.9, I only want to see the one test from
4/1/2005 for patient 223344 because that's the latest (maximum) date for
that patient and it's value is greater than 2, the value I passed in my
text1 parameter. My query above works, except for the part with
labs.result. Because I have to select that field in the select statement, I
also have to declare it in the GROUP BY clause. The GROUP BY part is
messing me up. Because I declared labs.result, and the values are
different, I get 2 records displaying later on. The result values are
different, so they're grouped differently.

Any ideas on how I can get around this? TIA!

Tom

--
 
Top