Create report grouping by "buckets"

[BibleMan]

I am looking to create a report from a large table, that shows the number of instances of a certain criteria on a weekly basis - grouped in 7 day buckets - over the course of an entire year.

Is a report the best place to start this? Maybe start with a query first that creates the "buckets"?

Any ideas as to how I could accomplish this would be appreciated.

 

[cygne]

I would create a query first. The query here groups by "week of the year", "day of the week", and criteria summing on the number of instances.

DateHappened is a Date field
Criteria is a Text field
TimesOccurred is a Number field

SELECT
DateDiff("ww",CDate("1/1/" & Year([DateHappened])),[DateHappened])+1 AS WeekOfYear,
Weekday([DateHappened]) AS DayOfWeek,
TableName.Criteria,
Sum(TableName.TimesOccurred) AS SumOfTimesOccurred
FROM TableName
GROUP BY
DateDiff("ww",CDate("1/1/" & Year([DateHappened])),[DateHappened])+1,
Weekday([DateHappened]),
TableName.Criteria;


I would create this query and use it in a query created for the report.

Hope this help...

 

[cygne]

Use the DatePart function to determine the Week Of Year:

DatePart("ww",[DateHappened])As WeekOfYear

This function was made for that operation. I forgot about the DatePart function -- it should be used instead of calculating using the DateDiff function.