henry
asked on
group by week access
Hello experts.
I have grouping by week in my report.
I need show in group header date range for each week instead number of week what I have right now.
Existing formula (showing number of week in year):
=formula$[(Date Worked)],"ww",0,0)
I' looking something like that instead:
Running report for march 2011
week 03-01-2011 thru 03-05-2011
detalis......
week ......etc
how to do that
thx
I have grouping by week in my report.
I need show in group header date range for each week instead number of week what I have right now.
Existing formula (showing number of week in year):
=formula$[(Date Worked)],"ww",0,0)
I' looking something like that instead:
Running report for march 2011
week 03-01-2011 thru 03-05-2011
detalis......
week ......etc
how to do that
thx
I have a table (tbl_Numbers) that I use for this purpose. It contains a single field (intNumber) and 10 records (the values 0 through 9). From this table, I create a query (qyr_Numbers) which generates numbers from 0 through 99 (easy to expand to 999 or larger) that looks like:
SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
from qry_Numbers, you can then create another query which gives you the week start and end dates for a given 100 day period (given the start date).
SELECT DatePart("ww",DateAdd("d", [intNumber ],[StartDa te])) AS RangeWeek,
Min(DateAdd("d",[intNumber ],[StartDa te])) AS WeekStart,
Max(DateAdd("d",[intNumber ],[StartDa te])) AS WeekEnd
FROM qry_Numbers
WHERE Weekday(DateAdd("d",[intNu mber],[Sta rtDate]),2 )<6
GROUP BY DatePart("ww",DateAdd("d", [intNumber ],[StartDa te]));
SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
from qry_Numbers, you can then create another query which gives you the week start and end dates for a given 100 day period (given the start date).
SELECT DatePart("ww",DateAdd("d",
Min(DateAdd("d",[intNumber
Max(DateAdd("d",[intNumber
FROM qry_Numbers
WHERE Weekday(DateAdd("d",[intNu
GROUP BY DatePart("ww",DateAdd("d",
Thanks fyed, mine was much more convoluted than yours...
;-)
Jeff
;-)
Jeff
henryk123,
Just keep in mind that anytime you do things "Weekly", you need to take into account the year.
For example, if your data spans multiple years, you could have week 12 for *all* years, in the same Group.
Jeff
Just keep in mind that anytime you do things "Weekly", you need to take into account the year.
For example, if your data spans multiple years, you could have week 12 for *all* years, in the same Group.
Jeff
...so your primary grouping should be the Year, then the week...
Can you confirm you want to do this for a given month for data to date in the current year?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'll dig it up tonight if another Expert does not chime in...
JeffCoachman