Access query problem

[JohnAtWork]

I think it needs to be done in VB, so this is the place, eh? :)

Here's the gist: I have data sorted in weekly buckets (demand per week) with static field names. (Week1, Week2, . . . Week39)

Week1 is always the current week.

I need to consolidate this into monthly buckets. . . anyone know how to do this?

(Note, this report could be ran any day of the month, and most likely will be run on a weekly basis. Therefore CurrentMonth could be anywhere from Week1-Week4 data, or only Week1)

[JHausmann]

For starters?

You only keep 9 months of rolling data or does it go all the way to week 52?

Do you get 1 record for the 9 months or 39 records?

52 weeks in a year
13 weeks in a quarter
Months usually fall into a 4-4-5 pattern in a quarter

You might consider building a table with 12 entries (1 for each month) that contains the start date for the month and the number of weeks in that month. For example

1,1/3/1999,4
2,1/31/1999,4
3,2/28/1999,5
4,4/4/1999,4
5,5/2/1999,4
6,5/30/1999,5
7,7/4/1999,4
8,8/1/1999,4
9,8/29/1999,5
10,10/3/1999,4
11,10/31/1999,4
12,11/28/1999,5

[JohnAtWork]

I got it!!!
I will post the code after I get the kinks worked out and it's doing what I want it to.

I'd really like some critiques on my code after it's done, so I can start tightening it up for later projects. . .