3 yr rolling in 5 week increments

[sxschech]

I have looked at FAQ703-5466 and seems close to what I'm doing, but not quite as the report I'm creating is not going by calendar weeks/months, rather by academic year, which straddles calendar years. Perhaps I am thinking in the wrong direction here, so any insight would be appreciated. I set up the crosstab and a report and they are working fine, but not sure how to "genericize" the headers without a complicated table. Output shows a rolling 5 week period for 3 yrs. Each week has a week number, however the week number corresponds to a semester and not a calendar year, so for example week ending 21-dec-2007 would be week 14.
I built a header table such as:

Yr01Wk01 200601
Yr01Wk02 200602
..
Yr02Wk02 200702

Here is a condensed sample output as of 07-Dec-2008
[tt]
2006 ----------| 2007 ----------| 2008----------
Wk10 Wk11 Wk12 | Wk10 Wk11 Wk12 | Wk10 Wk11 Wk12
LS A 5 5 6 | 2 2 5 | 0 0 1
B 2 2 2 0 0 1 0 0 0
...
DW A 18 18 22.....

[/tt]

The next week everything shifts over, week10 drops off and week13 is displayed
[tt]
2006 ----------| 2007 ----------| 2008----------
Wk11 Wk12 Wk13 | Wk11 Wk12 Wk13 | Wk11 Wk12 Wk13
LS A 5 6 8 | 2 5 6 | 0 1 3
B 2 3 4 0 1 2 0 0 0
...
DW A 18 22 26.....

[/tt]

The dates themselves are not displayed on report. I am showing here for reference to the week numbers in the example report above:
2006 2007 2008
Wk12 09Dec05 08Dec06 07Dec07
Wk11 02Dec05 01Dec06 30Nov07
Wk10 25Nov05 24Nov06 23Nov07


With present setup, using the crosstab header table the report refers to the values, Yr1Wk1 Yr2Wk2, etc. How would I get the date/weeks to fall in the correct bucket?

 

[sxschech]

As a followup to my previous post...
I think I have an idea, I looked at some threads and tried the samples, couldn't get them to work. If I can dynamically generate a counter or find some calculation that can convert the sequence of expr1, then the crosstab should work with generic names.

SELECT Right([WeekNo],2) AS Expr1, WeekTest.WeekNo
FROM WeekTest
WHERE (((Right([WeekNo],2)) Between '08' And '12'));

Expr1 WeekNo
08 200608
09 200609
10 200610
11 200611
12 200612
08 200708
09 200709
10 200710
11 200711
12 200712

Need something to have Expr1 be as follows

Expr1 WeekNo
01 200608
02 200609
03 200610
04 200611
05 200612
01 200708
02 200709
03 200710
04 200711
05 200712

Next week when I run the query, it would then be

SELECT Right([WeekNo],2) AS Expr1, WeekTest.WeekNo
FROM WeekTest
WHERE (((Right([WeekNo],2)) Between '09' And '13'));

Expr1 WeekNo
01 200609
02 200610
03 200611
04 200612
05 200613
01 200709
02 200710
03 200711
04 200712
05 200713

 

[dhookom]

What field are you using to create your column headings? Is it a date field or week number or what? Is it numeric or text?

I guess I would create a table that would look up the "week number". The weeks would be numbered uniquely to use as column headings.

Could you share some significant table and field names? How about the SQL view of any significant queries?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[sxschech]

Duane,

Thanks for responding. I thought about it over the weekend and have created a function to do the week conversion. In a quick test, seems to do what I need. Will be off until beginning of year, so I'll pick it up then and let you know if it works with real data.