Report to split into weeks?

[dk99]

Hi,

Help again please .. I have a report which shows the productivity for a team for the month, however Management have now asked for the report to subtotal each week together, as well as cumulate for the month. Any ideas how I would even start this, they are expecting something like.

Date No.
Monday 1st July 54
Tuesday 2nd July 44
.......
Total for the week 98

Monday 7th July 50
Tueday 8th July 30
.......
Total for the week 80

Total for the month 178

.... I am sure you get the idea.

 

[SkipVought]

Hi,

You can group dates into weeks using an expression like this...

WkDate: INT(([TheDateField]-2)/7)*7+2

will take any date and calculate the previous MONDAY.


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[dk99]

Sorry I might be being a bit thick here, but how would I use that?

 

[dhookom]

You could use Skip's expression in your sorting and grouping control source:
=INT(([TheDateField]-2)/7)*7+2
Or just
=DateFormat(TheDateField,"mm") & Format(Format(TheDateField,"ww"),"00")

Have you considered how you are going to display months that begin/end mid week?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]