Calculating 7 Day Averages

[rflanary]

I have a complicated report that i am trying to build in access and spit out a report in excel. I use sql server and access 2003.

I have a form that a user inputs a startdate and enddate. this is basically a range for a entire month.

I need some advice on how to calculate averages based on a 7 day week from sunday - saturday for an entire month. Each saturday in a month will have an average for the week.

What makes this complicated is the beginning week of the report if the first day of the week starts on Thursday. It needs to back up to the previous sunday and calculate from there.

The end of the month will stop on the last day.

any help of were to start would be appreciated.

 

[mihabaki]

Post some info on the table/s / query (especially the data contained) from which you're generating the report

m

 

[namliam]

Research the SQL function Datepart this function allows you to find out what day of the week it is, then simply substract the value from the date and you have your "aggrigation date".

Then it is a simple matter of a group by/avg query. Good luck ! && Welcome to AWF

 

[smig]

here some code to help:

------------------------------
function MoveToSuday(OldStartDay)

Dim DaysToMove as Integer
Dim NewStartDay as Date

DaysToMove = 1 - Format(OldStartDay, "w")
NewStartDay = DateAdd("d", DaysToMove, OldStartDay)

end function
------------------------------