UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Inner Join Automation In SQL On Vba Statement, Access 2013    
 
   
waitingroomz
post Mar 26 2019, 08:38 AM
Post#1



Posts: 93
Joined: 29-October 18



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.
Attached File(s)
Attached File  Example.zip ( 216.64K )Number of downloads: 18
 
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 07:03 PM