I'm trying to build a query in Microsoft Access and something weird is happening with the WHERE and HAVING clauses. While experimenting with using WHERE and HAVING on a date field (tblDailyFactor.Date) I discovered that including a filter in both the WHERE and HAVING clauses makes the query run significantly faster than simply including the filter in the WHERE clause alone. I used the VBA Timer function to measure the processing time and listed those below along with the two different sets of code.
Can anyone help me to understand why this is happening? I'd like to implement this into my query but I need to be able to explain and justify the code. Thanks!
Run time: 1.01 seconds
SELECT tblDailyFactor.Date, tblStaticData1.Name
FROM ((((tblSubTransactions INNER JOIN tblTransactions ON tblSubTransactions.ReferenceNumber = tblTransactions.ReferenceNumber)
INNER JOIN tblAccounts ON tblTransactions.LocalID = tblAccounts.LocalID)
INNER JOIN tblStaticData2 ON tblAccounts.LocalID = tblStaticData2.LocalID)
INNER JOIN tblStaticData1 ON tblStaticData2.GlobalID = tblStaticData1.GlobalID)
INNER JOIN tblDailyFactor ON tblStaticData1.GlobalID = tblDailyFactor.GlobalID
WHERE (((tblTransactions.Date)<=#8/31/2022#) AND ((tblAccounts.Status)='Active') AND ((tblDailyFactor.Date)=#8/31/2022#))
GROUP BY tblDailyFactor.Date, tblStaticData1.Name
HAVING ((Sum(tblSubTransactions.BalanceUSD))>0.009);
Run time: 0.16 seconds
SELECT tblDailyFactor.Date, tblStaticData1.Name
FROM ((((tblSubTransactions INNER JOIN tblTransactions ON tblSubTransactions.ReferenceNumber = tblTransactions.ReferenceNumber)
INNER JOIN tblAccounts ON tblTransactions.LocalID = tblAccounts.LocalID)
INNER JOIN tblStaticData2 ON tblAccounts.LocalID = tblStaticData2.LocalID)
INNER JOIN tblStaticData1 ON tblStaticData2.GlobalID = tblStaticData1.GlobalID)
INNER JOIN tblDailyFactor ON tblStaticData1.GlobalID = tblDailyFactor.GlobalID
WHERE (((tblTransactions.Date)<=#8/31/2022#) AND ((tblAccounts.Status)='Active') AND ((tblDailyFactor.Date)=#8/31/2022#))
GROUP BY tblDailyFactor.Date, tblStaticData1.Name
HAVING (((Sum(tblSubTransactions.BalanceUSD))>0.009) AND ((tblDailyFactor.Date)=#8/31/2022#));
(running Microsoft 365 MSO , Version 2110 Build 16.0.14527.20234 64-bit )