I'm building a query that needs to do quite a lot of sums. As a result, its execution time is around 8 seconds - which obviously isn't great! The sums add up either 7 or 21 DLookup values - all of which I have input separately.
Just wondering if there is a more efficient way of writing the sums to speed up execution. Here's a snippet of the code:
So, for the one where I need it to add up 21 values, it continues in the same vein until [WinDate]-x hits -21.
I'm pretty new to Access / SQL - hence my basic way of wring this sum - so, please make any suggestions pretty easy to understand / implement
Just wondering if there is a more efficient way of writing the sums to speed up execution. Here's a snippet of the code:
Code:
Nz(Dlookup("daysteps", "stepcount", "stepsdate=#" & Format(([WInDate]-1),"mm/dd/yyyy") & "# and membershipno=" & stepcount.membershipno), 0)+
Nz(Dlookup("daysteps", "stepcount", "stepsdate=#" & Format(([WInDate]-2),"mm/dd/yyyy") & "# and membershipno=" & stepcount.membershipno), 0)+
Nz(Dlookup("daysteps", "stepcount", "stepsdate=#" & Format(([WInDate]-3),"mm/dd/yyyy") & "# and membershipno=" & stepcount.membershipno), 0)+
Nz(Dlookup("daysteps", "stepcount", "stepsdate=#" & Format(([WInDate]-4),"mm/dd/yyyy") & "# and membershipno=" & stepcount.membershipno), 0)
I'm pretty new to Access / SQL - hence my basic way of wring this sum - so, please make any suggestions pretty easy to understand / implement
