Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Getting Weekly Totals in a Query
- Thread starter bwedmund
- Start date
I'm not really qualified to help but it seems many of our advisors are busy or on vacation.
If you already have a monthly query, why can't you just modify it to return a week's worth of data. Or maybe the quarterly would be more helpful if it has calculations. Use the same logic as what already exists.
If you already have a monthly query, why can't you just modify it to return a week's worth of data. Or maybe the quarterly would be more helpful if it has calculations. Use the same logic as what already exists.
MichaelRed
Programmer
You need to provide some additional detail to get a detailed response.
how is the data information stored/available? Id it in a Date/Time foramt in the table? Are the Day/Month/Quarter items stored in the table or are they 'calculated' on the fly in the 'program'?
A simple approach is to calculate the "WeekNumber" of each record. This is fairly straingh forward:
WkNo = DateDiff("d", DateValue("1/1/" & Year(Now)), Now()) \ 7
Of course, this ignores how you wnat to "define" the week. Especially the "First Week". Does it start on 1/1/Year(Now())? Is it the first full week of Year(Now())? ... (there are several possabilities).
Given that you HAVE decided on and adjusted for the start of the "WkNo", placing htis calculation in a field (table or query) would appear to provide you w/ the necessary sorting/grouping information.
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
how is the data information stored/available? Id it in a Date/Time foramt in the table? Are the Day/Month/Quarter items stored in the table or are they 'calculated' on the fly in the 'program'?
A simple approach is to calculate the "WeekNumber" of each record. This is fairly straingh forward:
WkNo = DateDiff("d", DateValue("1/1/" & Year(Now)), Now()) \ 7
Of course, this ignores how you wnat to "define" the week. Especially the "First Week". Does it start on 1/1/Year(Now())? Is it the first full week of Year(Now())? ... (there are several possabilities).
Given that you HAVE decided on and adjusted for the start of the "WkNo", placing htis calculation in a field (table or query) would appear to provide you w/ the necessary sorting/grouping information.
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
- Status
Similar threads
- Question
- Locked
- Question
