Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Searching + Data Mining _ Inner Join Automation In SQL On Vba Statement

Posted by: waitingroomz Mar 26 2019, 08:38 AM

Good morning everyone!

I am attempting to build an interactive search form with criteria based on dates.

I know the SQL I need for the different types of searches, but am unsure on how to build the string that will additionally deal with the correct number of parenthesis (on the FROM portion) based on the number of Inner Joins that will occur.

For example, If I'm looking at all three Tables (tblScreenings, tblTreatment, tblAftercare) my SQL will need to be:

CODE
    strSql = "SELECT DISTINCTROW tblPatients.PatientName,"
    strSql = strSql & "tblPatients.ActivePatient,"
    strSql = strSql & "DCount(""ID"",""tblScreenings"",""[PatientID]="" & tblPatients.[ID]) AS TotalScreenings,"
    strSql = strSql & "DCount(""ScreeningDate"",""tblScreenings"",""[PatientID]="" & tblPatients.[ID]) AS TotalScreeningDates,"
    strSql = strSql & "DMax(""ScreeningDate"",""tblScreenings"",""[PatientID]="" & tblPatients.[ID]) AS LastScreeningDate,"
    strSql = strSql & "DCount(""ID"",""tblTreatment"",""[PatientID]="" & tblPatients.[ID]) AS TotalTreatments,"
    strSql = strSql & "DCount(""TreatmentStart"",""tblTreatment"",""[PatientID]="" & tblPatients.[ID]) AS TotalTreatmentDates,"
    strSql = strSql & "DMax(""TreatmentStart"",""tblTreatment"",""[PatientID]="" & tblPatients.[ID]) AS DateTreatmentStarted,"
    strSql = strSql & "DCount(""ID"",""tblAftercare"",""[PatientID]="" & tblPatients.[ID]) AS TotalAftercare,"
    strSql = strSql & "DMax(""AftercareStart"",""tblAftercare"",""[PatientID]="" & tblPatients.[ID]) AS DateAftercareStarted,"
    strSql = strSql & "IIf(Not IsNull([LastScreeningDate])"
    strSql = strSql & " AND [TotalScreenings]=[TotalScreeningDates],Trim(""Screened"
    strSql = strSql & " ON "" & Format([LastScreeningDate],""mmm dd"""","
    strSql = strSql & """""yyyy"")),IIf([TotalScreenings]>0,""Screening Pending"",""No Screening History"")) AS ScreeningInformation,"
    strSql = strSql & "IIf(Not IsNull([DateTreatmentStarted])"
    strSql = strSql & " AND [TotalTreatments]=[TotalTreatmentDates],Trim(""Treatment started"
    strSql = strSql & " ON "" & Format([DateTreatmentStarted],""mmm dd"""","
    strSql = strSql & """""yyyy"")),IIf([TotalTreatments]>0,""Treatment Pending"",""No Treatment History"")) AS TreatmentInformation,"
    strSql = strSql & "IIf([TotalAftercare]>0,Trim(""Aftercare started"
    strSql = strSql & " ON "" & Format([DateAftercareStarted],""mmm dd"""","
    strSql = strSql & """""yyyy"")),""Not enrolled"") AS AftercareInformation,"
    strSql = strSql & "IIf(IsNull([CommandAssigned]),""No Command Assigned"",DLookUp(""CommandName"",""tblCommands"",""[ID]="" & [CommandAssigned])) AS AssignedTo,"
    strSql = strSql & "tblPatients.*,"
    strSql = strSql & "tblAftercare.AftercareStart,"
    strSql = strSql & "tblScreenings.ScreeningDate,"
    strSql = strSql & "tblTreatment.TreatmentStart"
    strSql = strSql & " FROM ((tblPatients"
    strSql = strSql & " INNER JOIN tblScreenings"
    strSql = strSql & " ON tblPatients.ID = tblScreenings.PatientID)"
    strSql = strSql & " INNER JOIN tblTreatment"
    strSql = strSql & " ON tblPatients.ID = tblTreatment.PatientID)"
    strSql = strSql & " INNER JOIN tblAftercare"
    strSql = strSql & " ON tblPatients.ID = tblAftercare.PatientID"
    strSql = strSql & " WHERE (((tblPatients.ActivePatient)=True)"
    strSql = strSql & " AND ((tblAftercare.AftercareStart)"
    strSql = strSql & " BETWEEN Forms!frmPatientDashboard!txtDateFrom"
    strSql = strSql & " AND Forms!frmPatientDashboard!txtDateTo)"
    strSql = strSql & " AND ((tblScreenings.ScreeningDate)"
    strSql = strSql & " BETWEEN Forms!frmPatientDashboard!txtDateFrom"
    strSql = strSql & " AND Forms!frmPatientDashboard!txtDateTo)"
    strSql = strSql & " AND ((tblTreatment.TreatmentStart)"
    strSql = strSql & " BETWEEN Forms!frmPatientDashboard!txtDateFrom"
    strSql = strSql & " AND Forms!frmPatientDashboard!txtDateTo))"
    strSql = strSql & " ORDER BY tblPatients.PatientName;"


However, if I only use the first and last table, I'll need to drop a set of Parenthesis, like so:

CODE
    strSql = "SELECT DISTINCTROW tblPatients.PatientName,"
    strSql = strSql & "tblPatients.ActivePatient,"
    strSql = strSql & "DCount(""ID"",""tblScreenings"",""[PatientID]="" & tblPatients.[ID]) AS TotalScreenings,"
    strSql = strSql & "DCount(""ScreeningDate"",""tblScreenings"",""[PatientID]="" & tblPatients.[ID]) AS TotalScreeningDates,"
    strSql = strSql & "DMax(""ScreeningDate"",""tblScreenings"",""[PatientID]="" & tblPatients.[ID]) AS LastScreeningDate,"
    strSql = strSql & "DCount(""ID"",""tblTreatment"",""[PatientID]="" & tblPatients.[ID]) AS TotalTreatments,"
    strSql = strSql & "DCount(""TreatmentStart"",""tblTreatment"",""[PatientID]="" & tblPatients.[ID]) AS TotalTreatmentDates,"
    strSql = strSql & "DMax(""TreatmentStart"",""tblTreatment"",""[PatientID]="" & tblPatients.[ID]) AS DateTreatmentStarted,"
    strSql = strSql & "DCount(""ID"",""tblAftercare"",""[PatientID]="" & tblPatients.[ID]) AS TotalAftercare,"
    strSql = strSql & "DMax(""AftercareStart"",""tblAftercare"",""[PatientID]="" & tblPatients.[ID]) AS DateAftercareStarted,"
    strSql = strSql & "IIf(Not IsNull([LastScreeningDate])"
    strSql = strSql & " AND [TotalScreenings]=[TotalScreeningDates],Trim(""Screened"
    strSql = strSql & " ON "" & Format([LastScreeningDate],""mmm dd"""","
    strSql = strSql & """""yyyy"")),IIf([TotalScreenings]>0,""Screening Pending"",""No Screening History"")) AS ScreeningInformation,"
    strSql = strSql & "IIf(Not IsNull([DateTreatmentStarted])"
    strSql = strSql & " AND [TotalTreatments]=[TotalTreatmentDates],Trim(""Treatment started"
    strSql = strSql & " ON "" & Format([DateTreatmentStarted],""mmm dd"""","
    strSql = strSql & """""yyyy"")),IIf([TotalTreatments]>0,""Treatment Pending"",""No Treatment History"")) AS TreatmentInformation,"
    strSql = strSql & "IIf([TotalAftercare]>0,Trim(""Aftercare started"
    strSql = strSql & " ON "" & Format([DateAftercareStarted],""mmm dd"""","
    strSql = strSql & """""yyyy"")),""Not enrolled"") AS AftercareInformation,"
    strSql = strSql & "IIf(IsNull([CommandAssigned]),""No Command Assigned"",DLookUp(""CommandName"",""tblCommands"",""[ID]="" & [CommandAssigned])) AS AssignedTo,"
    strSql = strSql & "tblPatients.*,"
    strSql = strSql & "tblAftercare.AftercareStart,"
    strSql = strSql & "tblScreenings.ScreeningDate"
    strSql = strSql & " FROM (tblPatients"
    strSql = strSql & " INNER JOIN tblAftercare"
    strSql = strSql & " ON tblPatients.ID = tblAftercare.PatientID)"
    strSql = strSql & " INNER JOIN tblScreenings"
    strSql = strSql & " ON tblPatients.ID = tblScreenings.PatientID"
    strSql = strSql & " WHERE (((tblPatients.ActivePatient)=True)"
    strSql = strSql & " AND ((tblAftercare.AftercareStart)"
    strSql = strSql & " BETWEEN [Forms]![frmPatientDashboard]![txtDateFrom]"
    strSql = strSql & " AND [Forms]![frmPatientDashboard]![txtDateTo])"
    strSql = strSql & " AND ((tblScreenings.ScreeningDate)"
    strSql = strSql & " BETWEEN [Forms]![frmPatientDashboard]![txtDateFrom]"
    strSql = strSql & " AND [Forms]![frmPatientDashboard]![txtDateTo]))"
    strSql = strSql & " ORDER BY tblPatients.PatientName;"


Is there a way to do this easily? I'm using a Case Select for option groups to determine variations.

Attached database as well.

 Example.zip ( 216.64K ): 17
 

Posted by: ranman256 Mar 29 2019, 08:26 AM

have you tried making it easy and just make a query (not SQL) ?

Posted by: projecttoday Mar 29 2019, 10:34 AM

I suggest a nested datasheet for this.

Posted by: waitingroomz Apr 1 2019, 07:55 AM

Nested Database, could you explain?

The Nz Function I am using is what is really slowing me down.