Getting Weekly Totals in a Query

[bwedmund]

I am using access 97 and would like to know how I can get weekly totals using a query? The program is already set up for unique, daily, monthly, quarterly, yearly.

Thanks for any help

 

[jendendav]

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.

 

[MichaelRed]

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