Consulting

Results 1 to 1 of 1

Thread: Advanced SQL Sorting

  1. #1
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399

    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]
    Last edited by Movian; 12-29-2011 at 11:24 AM. Reason: Mistype on Title
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

Posting Permissions

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