A date challenge only for the brave

[tyronedjackson]

I have a table with two columns called FromDate and ToDate indicating the start and end of a student's program. Since the client sells it's programs by weeks, they would like a report that shows the number of weeks sold for every week of the year.

The challenge is to get a crosstab query to reflect a count of 1 in week36, week37, and again in week38 for a student who has a FromDate = 05-sept-2010 and a ToDate = 25-sept-2010.

I can't figure it out so any help would be great.

 

[the_net_2.0]

This cannot be done with a crosstab query simply due to the fact that it requires additional record creation. The query utility cannot do this by itself.

As an alternative, you can group your records on the start date and add a dynamic column at the end that indicates how many weekly periods occur between the two dates. This can be done by writing:

SELECT (Format([end date], "ww") - (Format([start date], "ww") + 1

     As [Total Number of Weeks]

That number can be useful if you can pull the weekly sales amount from another place in order to produce the report. Additionally, the chart wizard is not able to produce 0s for missing time periods, so the records have to be created to compensate for that.

 

[gemma-the-husky]

try the seldom used, but useful partition, with a value of 7, as a base query. then use this to generate your totals

 

[the_net_2.0]

thank you very much for pointing that out! That really is useful. I didn't know it existed.