GroupOn something other than EachValue in reports

[ALSav]

Hi,
I am using the report tool in MS Access 2002
I have some data that is collected in weekly cycles and I would like to produce a report that groups each week and calculates totals.
my fields in my query are
route
date
dayno (calculated from date with the vb Weekday function)
day (from weekdayName)
weight_collected

I would like to obtain weekly totals but not sure how to use the GroupOn function.

Any ideas??

thanks
Alsav

 

[jmeadows7]

In your query, you can use the datepart function to return the week of the year concatenated with the year.
i.e.
WeekId:clng(datepart("yyyy",[date]& DatePart("w", [date],1) )

This will yield week id on which you can group. I included the year so if your dates overlap the year end it will still be in the correct order.
i.e. 200351
200352
200401

HTH

 

[jmeadows7]

ooops - cutting and pasting - parens were probably wrong:

WeekId:clng(datepart("yyyy",[date])& DatePart("w", [date],1))

 

[RoyVidar]

Hi ALSav!

There's a report forum where this question perhaps might be better addressed (forum703).

If you select to group by your date, then in the sorting and grouping dialog, select "Week" in the Group On property. Then use an unbound textcontrol in the date footer with the controlsource of

[tt]=sum(fieldname)[/tt]

- where fieldname is the name of the field you want to calculate

BTW - you should avoid using "date" as name of fields or controls, since it is a property of (almost?) all objects.

Roy-Vidar

 

[ALSav]

Thanks for the tips, I'll have another go at it.

regards

ALSav