Advanced SQL Sorting

Hey i have a complex VBA form that generates SQL statements to produce lists. I have been given some tough guidelines as to what these statements have to be able to do

Here is a standard example

List #1 – All Elements are “AND-ed” together; Sort on total cholesterol result (descending order)
· Demographics: Patient age < 50 years old
· Problem: Coronary artery disease (CAD)
· Medication: Lisinopril
· Lab Results: Total cholesterol > 200 mg/dl
· Lab Results: HDL-cholesterol < 40 mg/dl
· Lab Results: LDL-cholesterol > 100 mg/dl
· Lab Results: Triglycerides > 150 mg/dl

So my form has to generate a SQL statement that produces a list based on multiple tables with both 1 -> 1 and 1->M relationships.

I have the first part down as shown with the example bellow searching for both the Total Cholesterol > 200 Mg AND HDL-Cholesterol < 40 section. However i am having trouble sorting by total Cholesterol . The name of the test 'Total Cholesterol ' is a field within the lab results table. And as such i would need to determine if a given record's testname = 'Total Cholesterol' before i order by result asc.

Here is some sample info from tblPatient

ID Name Age
07 Mile Smith 26
1010 Gina Smith 32

Here is some sample info from the tbllab results
ID TestName Result
07 Total Cholesterol 300
07 HDL-Cholesterol 35
07 LDL-Cholesterol 150
07 Triglycerides 160
1010 Total Cholesterol 325
1010 HDL-Cholesterol 41




Any suggestions would be apriciated.

[vba]SELECT [tblPatient].[MedicalID#], [tblpatient].[Firstname], [tblpatient].[Lastname], [tblpatient].[Age], tblLabResults.TestName, tblLabResults.Result
FROM tblPatient
INNER JOIN tblLabResults
ON [tblpatient].[MedicalID#] = [tblLabResults].[MedicalID]
WHERE[tblpatient].[Age] > 25 AND
(SELECT COUNT(*) FROM tblPatient INNER JOIN tblLabResults ON [tblpatient].[MedicalID#] = [tblLabResults].[MedicalID] AND ([tblLabResults].[TestName] = 'Total Cholesterol' AND [tblLabResults].[Result] > 200)) > 0 AND
(SELECT COUNT(*) FROM tblPatient INNER JOIN tblLabResults ON [tblpatient].[MedicalID#] = [tblLabResults].[MedicalID] AND ([tblLabResults].[TestName] = 'HDL-Cholesterol' AND [tblLabResults].[Result] < 40)) > 0
[/vba]