AFAICT you're using
DAvg
and
DCount
functions
outside the query. On SQL Server I would use a windowing function to calculate the average/rank by approver, and perhaps even pivot the result so that I have
JW_DAYRNK
,
CD_DAYRNK
, ...,
JW_DAYQTY
, ...,
BE_DAYQTY
columns in the recordset - i.e. let the query to the calculations, not external functions. Not sure how that would be done in Access /without windowing functions though - I guess it involves a number of sub-queries... obviously not optimal. If your backend is SQL Server, make a T-SQL view or stored procedure for it. —
Mathieu Guindon 1 min ago