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"));
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.