Averages (1 Viewer)

[Gorio]

In a query, I have a large list of records that I need to breakdown and examine for when they occur, by year, month, day of week, and eventually hour of day.
I've identified which records that need to be included with an IIf statement. If it qualifies for examination it recieves a "1" else a "0".
In the report I can group and sort appropriately thanks to Pat, however when I try to determine averages I am getting inappropirate numbers.
What I currently need is the average number of records occuring on specific weekdays per month. Ex: Average Number of Incidents on Fridays in March.
I can sum the number of incidents, but cannot manage to determine the number of times a Weekday occurs in any given month to determine the average.
Any suggestions or ideas would be greatly appreciated.

 

[llkhoutx]

Build and populate non-displayed columns in your query with Year, Month, Week, HourOfTheDay, etc. and groiup and sum as appropriate.

 

[Gorio]

I've built the query to breakdown by year, month, day and hour. I can group and sum appropriately. It is the averaging that is giving me grief. I want to show the average number of incidents on any given Day of the week, based on Year and Month.

If I have number of possible incidents that occurred, sort through these to determine true incidents, then try and group and sort, the result is an average that appears to be based on dividing the number of true incidents by possible incidents. This creates a percentage of true incidents.

What I need is the average of true incidents occuring on the Days of the Week in a month. So it appears that I need to divide the true incidents by the number of times that particular day of week occurs in a month.

It shouldn't be as hard as I'm managing to make it appear.