More Efficient Way To Write This DLookup Sum?

seantnash

New member
Local time
Today, 21:23
Joined
Aug 21, 2016
Messages
9
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:

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)
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 :confused:
 
Can you explain in simple English what your data is and what result you are trying to get to?
 
use:

DSum("daystep","stepcount","membershipno=" & [membershipno] & " And
stepsdate between #" & Format([WinDate]-21,"mm/dd/yyyy") & "# And #" & Format([WindDate]-1, "mm/dd/yyyy") & "#")
 
Last edited:
I have a table that contains different people's step count per day. I then need the query to calculate the total number of steps for a person for both the previous week and 3 weeks before the WInDate (a changeable date of when they last visit)
 
Arne's will work - or you could use a totals query and just add the two dates as where criteria.
e.g. Between Date() AND DateAdd("d",-21,Date())
 
You're a genius arnel - sooooooo much faster now :) Thank you so much (again!) :D
 

Users who are viewing this thread

Back
Top Bottom