Group by month or develop monthly average?

[BrokenBiker]

Hello, all.

I posted this about a month ago, but at that time I was running myself ragged and through too many problems at once. I stepped back and made some good progess. I put this in the General forum because it could encompass VBA, queries, and reports

I have a main report (Percentage Report) that has 4 subreports in it. Each subreport is based on a query that's run from three other queries. Its a neatly tangled mess, but it works fine.

The queries all count and calculate percentages for a pass rate of inspections on maintenance. There's an over-all/basic percentage that simply totals everything and divides for a percentage. There's also a "maintenance" percentage that only takes into account inspections done on maintenance (as opposed to various programs and processes.) Those both work fine for any given time period.

The third (and final) percentage deducts 0.5 points for each of a specific list of inspections (safety and other violations.) This works fine so long as you're only looking at a month's worth of data. The problem comes when you want to view any time period larger than that (quarter, semi-annual, annual.)

Basically, you end up subtracting a sum from an average and you end up w/ totally inaccurate numbers. I just can't quite figure out how to effectively either group by month or how to average the deductions based on the months covered.

I just finished completing this whole thing, and I'm pretty much done for tonight. Any help would be great.
----------------------------------------
Key words: sum totals, report grouping, report conditional format, alternate row colors (greenbar), count, calculate, percent

 

[BrokenBiker]

I should point out a couple of things real quick. The best dates to use for the example are any one-month period up to the end of July.

If you use the month of July as the timeframe, you'll see that the 317AG received a basic rate of 87.03%. When you include the deductions (4.5 points) their overal/final rate is 82.53%.

However, if you broaden that timeframe from the beginning of the year to the end of July, you get a much different picture. Their basic rate for this timeframe is 85.58%. But because I can't group by month or average the deductions, the points total to a grossly misleading figure (36.5 points resulting in a final rate of 49.08%,) when in actuality those 36.5 points could either be separated by month (along w/ the percentages) or averaged to 5.2 points resulting in a final rate of 80.38%.

I know this is a pretty complicated compilation of issues. Thanks for the views.

 

[BrokenBiker]

Well, I got it to include a monthly average of deductions based on the number of days (30 days per month.) I used the DateDiff function found in Pat's useful date functions example.

Ideally, I'd like it to group and calculate by month over a period of up to one year, but I really don't know how to go about that. The attachment to this thread includes an updated copy of the db.