Monthly report with weekly totaSs

[Alex Motilal]

Dear Members,
I have got a report with four columns. The first column is the date column, the second raw cotton issued, the third lint generated and the fourth seed generated.



The report is a monthly report, with the monthly totals in the report footer section.
What I want is it should show the weekly group totals, e.g., for January 1^st to 7^th, 8^th to 14^th, 15^th to 21^st 22^nd to 28^th and 29^th to 31^st. For a particular date where there is no processing, it should return ‘0’.
For the month of February it should be the days based on the leap year.
Please inform if it is possible & if so the solution.

Regards
Alex

[June7]

Won't be simple. Your 'week' is not based on a standard 7 days, like Sun - Sat and not every 'week' has 7 days. Need a field that has week identifier. Can be calculated.
Showing data where there are no records is another complication.

[Alex Motilal]

Thanks June7
If it can be done based on standard weeks and no need to show nil records, please intimate the solution.
Regards
Alex

[June7]

Date of the first day of week that any date falls in can be calculated.

What day do you want to be the beginning of a week - Sunday? This will give you 7-day weeks but some will cross months and years

FirstDayofWeek: ([DateField] - Weekday([DateField]))+ 1

Or use DatePart to pull year week number:

YrWk: Year([DateField]) & DatePart("ww", [DatePart])

Again, some weeks will cross months but will not cross years and 2 weeks can be less than 7 days.

Unfortunately, no intrinsic function to pull the month week number, which is actually your original requirement and can also be calculated, like:

YrMoWk: Year([DateField]) & Format([DateField]),"mm") & Switch(Day([DateField])<=7,1, Day([DateField])<=14,2, Day([DateField])<=21,3, Day([DateField])<=28,4, Day([DateField])<=31,5)

Use whichever as grouping criteria.