Week Totals

[Azyrus]

Hey Guys its me again =P

My database is coming along very nicely thanks to the help of some great people in this forum.



My latest challenge...

I have three tables, one for "Jobs" one for "Plumbers who work on a job" and one as a "unique plumber list".

I want to create a total of each week in my report at the moment the report shows all the jobs, and each day, each job has a job cost and at the bottom of the day is a day total. What I need now is a week total and further on a month and year total.

Can anyone shed some light on how to create a weekly total expression.

Thanks!

[apr pillai]

Create a Report Query with the Table and create three new Columns (for week, month and Year) with the following expressions:

1. DatePart("ww",[WorkedDate],1)

There are 53 Weeks in a Year. Each date in a Year falls within a week's period. For example 8th Jan, 2011 returns 2 with the above expression. Dates from 8 to 14 Jan will return the value 2 only. Dec. 25 to Dec. 31 falls within the week 53 and any date within this period will return the value 53. If you use this column on the Report and include in the Sorting and Grouping, you can simply write the expression =Sum([JobCost]) in the Group Footer to get the weekly Total.

The same method can be used for Monthly and Yearly Totals.

2. DatePart("m",[WorkDate],1) - second column for month values.

3. DatePart("yyyy",[Workdate,1) - third column for year values.

The third parameter value 1 indicates that the week day starts on Sunday (this is the default value).

[Azyrus]

Hello Apr Pillai,

So thankful for your answer, It worked perfectly!

Thank you very much.