Combining multiple select statements in one SQL Query? ConcatRelated() and Sum()

Question

We have a database that we use for tracking data and sending communications on a shared network drive. Recently we’ve had to incorporate a process to prefill out forms for an M1 Visa Application that has fields we do not track by in our tables but can be calculated based on the information we do track.

I have created several SQL Queries that pull data from two tables to produce certain field formats required on the application. All the queries pull from the same two tables. When I have the database on my desktop, it functions incredibly. However, when I put on the shared network drive for all users to access, the new functions I created take forever to produce results. The database works normally for everything except when utilizing the new SQL Queries I created.

I read online that the database is likely slowing down because it’s having to run 3-4 queries before adding the produced results to the form. I am asking today how to combine my multiple SQL Queries into one, to hopefully reduce the wait time.

I am not familiar with SQL or database coding really. Everything in our system works because I spend hours reading and using trial/error to produce the results needed. Posting the SQL code for the main two queries I need to combine below in hopes that someone can help me figure out how to get it to work. I've spent the past two days googling and reading, only to come up with I may need statements like CASE, UNION ALL, or something of that sort. But I am at a complete loss. Please help! Thank you!!

Query 1

SELECT DISTINCT Request.PermNumber, Request.Status,
     Request.Class, Request.StartDate, Request.Days, 
     ConcatRelated("Class", 
         "MALTESTConfirmationInformationQry2022",
         "PermNumber= " & Request.PermNumber) AS AllClasses
FROM Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
WHERE (((Request.Status)="Confirmed") AND ((Request.StartDate)>Date()))
ORDER BY Request.StartDate;

Query 2

SELECT Request.PermNumber, Request.Status, Sum(Request.Days) AS TotalDays
FROM Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
GROUP BY Request.PermNumber, Request.Status 
HAVING (((Request.Status) Like "Confirmed"));

Answer 1

I finally got around to looking at this in more depth. Here is the final solution that should run very quickly. I have not been able to fully test it because I do not have any data examples and I have had to make a few assumptions as a result. However, any issues you might come across should be quite minor. Copy the below code into a module:

'**********************************************************************************
' Parameters:
' intAction: 1 = Returns classes string to the query
'            2 = Load data into the class string collection
'            3 = Housekeeping. Will explicitly clear the classes collection
'
' strPermNumber: PermNumber used to retrieve the classes string from the collection.
'                Mandatory if intAction = 2, otherwise optional
' **********************************************************************************
Public Function fClassConcat(intAction As Integer, _
                             Optional strPermNumber As String) As String

  Dim rsClass           As DAO.Recordset, _
      strLastPermNumber As String, _
      strClasses        As String

  ' Static declaration. Collection will remain populated
  ' until explicitly cleared.
  Static colClasses As Collection

  Select Case intAction
    Case 1  ' Return string of classes
      fClassConcat = colClasses(strPermNumber)

    Case 2  ' Initialisation
      fClassConcat="@NEVER_EQUAL@"
      strClasses = ""
      Set colClasses = Nothing
      Set colClasses = New Collection

      ' The result of query MALTESTConfirmationInformationQry2022 must be ordered
      ' by PermNumber and Class. If this is already the case then simply replace the
      ' following line with: Set rsClass = CurrentDb.OpenRecordset(MALTESTConfirmationInformationQry2022, dbOpenDynaset)
      Set rsClass = CurrentDb.OpenRecordset("SELECT PermNumber, Class " & _
                                            "FROM MALTESTConfirmationInformationQry2022 " & _
                                            "ORDER BY PermNumber, Class;", dbOpenDynaset)
      With rsClass
        If Not .BOF Then
          .MoveFirst

          ' Force first loop
          strLastPermNumber = CStr(!PermNumber)

          Do While Not .EOF

            ' PermNumber has not changed so keep accumulating the class
            If CStr(!PermNumber) = strLastPermNumber Then
              strClasses = strClasses & !Class & ","

            ' Change of PermNumber. Add the class string (minus the trailing comma)
            ' to the collection and apply the PermNumber to the key.
            Else
              colClasses.Add Left(strClasses, Len(strClasses) - 1), strLastPermNumber

              ' Save the current PermNumber
              strLastPermNumber = CStr(!PermNumber)

              ' Start accumulating classes for the new PermNumber
              strClasses = !Class & ","

            End If

            .MoveNext
          Loop

          ' Add the last class string to the collection
          colClasses.Add Left(strClasses, Len(strClasses) - 1), strLastPermNumber

        End If

        .Close

      End With

      Set rsClass = Nothing

    Case 3  ' Free up memory (optional. Just call this function with intAction=3 after query execution)
      Set colClasses = Nothing

  End Select

End Function

And now modify the query as shown below or just copy and paste it:

SELECT DISTINCT Request.PermNumber, Request.Status,
     Request.Class, Request.StartDate, Request.Days, 
     (SELECT TOP 1 Sum(T1.Days) AS SumDays
      FROM Request AS T1
      GROUP BY T1.PermNumber, T1.Status 
      HAVING T1.PermNumber = Request.PermNumber AND T1.Status = Request.Status;) AS SumRequestDays, 
      fClassConcat(1, [Request].[PermNumber]) AS AllClasses
FROM Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
WHERE Request.Status="Confirmed" AND Request.StartDate>Date() AND fClassConcat(2)
ORDER BY Request.StartDate;

I think you will be pleasantly surprised.

Answer 2

Now that everyone has finished bashing you and arguing over the semantics of your question and tags, try this solution which should answer your original question of combining two queries into one. The result will give you an additional column (SumRequestDays) which contains the total days for the employee/status combination although you may not see much of a performance improvement. As @Pointy mentioned, you should check the indexes on your tables. A combined index of PermNumber and Status on the Request table would boost performance:

SELECT DISTINCT Request.PermNumber, Request.Status,
     Request.Class, Request.StartDate, Request.Days, 
     (SELECT TOP 1 Sum(T1.Days) AS SumDays
      FROM Request AS T1
      GROUP BY T1.PermNumber, T1.Status 
      HAVING T1.PermNumber = Request.PermNumber AND T1.Status = Request.Status;) AS SumRequestDays, 
     ConcatRelated("Class", 
         "MALTESTConfirmationInformationQry2022",
         "PermNumber= " & Request.PermNumber) AS AllClasses
FROM Employee INNER JOIN Request ON Employee.PermNumber = Request.PermNumber
WHERE Request.Status="Confirmed" AND Request.StartDate>Date()
ORDER BY Request.StartDate;

I believe your main bottleneck is the ConcatRelated function which I also believe originated from Allen Browne. Putting a function into a query will always result in a performance hit. You might want to consider a pre-execution option to prepare the data in advance by buffering it and only having to retrieve one line rather than building and performing a full-on query for every employee. It would be a quite straight-forward task to modify Allen's code to initialise and populate a Static Collection which would hold all the concatenated classes for each employee and then include some code to retrieve the employee item from the Collection. Initialise and retrieve actions can be controlled by a single additional parameter. The initialisation can be placed into the SQL WHERE clause which the SQL engine conveniently only executes once and the retrieve action would go into the SELECT clause.