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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 92
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: 17
 
Go to the top of the page
 
ranman256
post Mar 29 2019, 08:26 AM
Post#2



Posts: 903
Joined: 25-April 14



have you tried making it easy and just make a query (not SQL) ?
Go to the top of the page
 
projecttoday
post Mar 29 2019, 10:34 AM
Post#3


UtterAccess VIP
Posts: 11,061
Joined: 10-February 04
From: South Charleston, WV


I suggest a nested datasheet for this.

--------------------
Robert Crouser
Go to the top of the page
 
waitingroomz
post Apr 1 2019, 07:55 AM
Post#4



Posts: 92
Joined: 29-October 18



Nested Database, could you explain?

The Nz Function I am using is what is really slowing me down.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 08:18 PM