Hi Guys,

Been a while since I posted so I hope you are all well http://www.access-programmers.co.uk/ubb/smile.gif

Here's what I am trying to achieve: I work in telcoms and need to design a report that shows me the minutes for a set of telephone numbers over the last nine weeks for trend analysis.

So basically it looks something like this:

Tel.# week1 week2 week3 etc.....
1234 10 20 30

All the records I will draw on are by day. Is there someway I can get the (and I apologise if this should be posted in the reports forum) the underlying querie to count backward for 63 days from the current day and then put a week number tag against the right records. So it says something like Now()-63 in date criteria and then a field called week number that says between Now() and Now()-7 equals "Week 1" etc as an IIF statement.

Please help me out with this guys!!! Any help is much appreciated http://www.access-programmers.co.uk/ubb/wink.gif

Cheers,

Tom.

Create a new summary query using your existing table/query as the source.

Define a new field like

TheWeek: DatePart("ww",YourDateField)

and ensure the Total row for that field is set to "Group By".

Add a field for YourDateField and set the Total row value to "Where" and put "Date()-63" in the Criteria row to limit record selection to the most recent 9 weeks.

Add the field(s) you are summing up, with the Total Row set to "Sum"

Use this as the source for your report.


Alternately, you could just create a select query with a calculated field with the datepart() function, and do your summing and filtering in your report.

I just realized that the criteria I described is going to only select a portion of the 1st week you want. Try the following criteria instead:

TheWeek - DatePart("ww", Now() ) > 9 {or maybe 8 if you are counting this current week in your nine weeks}

This works great! Thanks a million KK http://www.access-programmers.co.uk/ubb/smile.gif

Time to go wow my boss http://www.access-programmers.co.uk/ubb/wink.gif

Hope you and everyone else on this forum have a great Christmas and a prosperous New Year!!!

Tommy B

I'm getting sloppy. My solution is going to fail when you get into the new year, as the week will reset to '1'! You need have a week value that accumulates with the year.

Redefine the formula for TheWeek as follows:

TheWeek: DatePart("ww",YourDateField) + Year(YourDateField)*52

Redefine the last criterion to read:

TheWeek - ( DatePart("ww", Now()) + Year(now())* 52 > 9

Thanks mate http://www.access-programmers.co.uk/ubb/wink.gif

Got it ;0

Have a great Xmas!