I have one table "Sales" in which orders are kept. Along with each record I have a field "weekly_sales" which calculates total sales for that week. The calculation uses another field "first_day_of_week"

What I need is a layout that shows only one entry for each week.

For Example:

Week Beginning on: Sales

9/28/2008 $1,000,000,000

10/5/2008 $0.01

10/12/2008 -$2E1000

Cant seem to wrap my head around this one

Thanks in advance

Edited October 7, 200817 yr by Guest

You need to create a sub summary report in order to summarize your sales by the field you are sorting by. You can create field that is the YYYYWW which is the YEAR and week of Year and then sort by that field.

Sort field = Year(datefield) & Right ("00" & WeekofYear(datefield) ; 2)

It would be better to use:

Datefield - DayOfWeek ( Datefield ) + 1

with the result type set to Date. After all, December 31, 2007 and January 1, 2008 ARE in the same week - even though they would return "200753" and "200801" respectively when using Year and WeekOfYear.

nice technique.