Splitting data into Week columns

[josephwc]

Microsoft Access XP

I am trying to create a query that takes

[Hours remaining] on a project, looks at todays date and the project [StopDate] and evenly distributes these remaining hours into Weekly data columns.

For example project ABC has 20 hours remaining and has a [StopDate] of 9/3/2006. The columns would look like this.

Week1 Week2 Week3 Week4 Week5 Week6
5 5 5 5 0 0

Any ideas?

 

[SkipVought]

This would be REAL EASY in Excel using a PivotTable and GROUPING your Date field by Days in incriments of 7. Could be done in about 15 seconds.

Skip,
[sub]
[red][/red]
[/sub]

 

[josephwc]

Hello SkipVought,

Thank you for responding.

If I put in a Pivot Table it is going to lump all the [Hours Remaining] in the week of the [Stop Date]. What I need to do is evenly distribute the [Hours Remaining] across the weeks from todays date through the week of the stop date.

 

[SkipVought]

Au contraire!

It will spread across the date range if you chose DAYS and Number of days = 7.

Did you OVERWRITE the Start At date in the Grouping window?

Skip,
[sub]
[red][/red]
[/sub]