Calculating Totals by Week

[clmarks]

I have a report which is run monthly. It is a list of clients for which we have provided services during the previous month and it totals the hours of service per client and at the end of the report. I now need for it to sub-total within each client's transactions for each calendar week during the month.

I'm using Access 2003. Can anyone tell me how to do this?

Thanks for your help.

cherry

 

[Trevor G]

Depending on the fields you have available, in the report you can use the view menu and grouping then select the week week field set the section below to group

you get a new grouping in design view, then grab your client field and place it in the same group header then add a calculation field to do the totals.

Trevor

 

[clmarks]

All I have is the transaction date. I have to determine the weeks based on Sunday thru Saturday regardless of which day of the week was the first day of the month.

I currently pull the data for the month from my transactions table and creating a temporary table to use in generating my report. I'm currently thinking that I can make a field in the temporary table for the week number and then run an update query against it to plug in the week number based on the date. I know it sounds a bit primative, but I haven't been able to think of anything else.

Am I way off base?

Thanks.

Cherry

 

[Trevor G]

That sounds the right plan, if you look up datediff or datepart in the formula side this will help you determine week numbers.

Trevor