Average of an Average

[techherbie]

Hello,

I am working on a database that I created years ago in ACCESS 2002 for my ex-wife's science fair. I had a number of books and figured it out. Now 8+ years later, I am trying to change it and can't remember how I did what I originally did.

Here is my issue, I need to generate 2 reports, first is a report that is sorted by science fair category, such a microbiology, computer science, physics, etc. Each student is judged 3 times, each of the scores are entered into the database in fields sc1, sc2 & sc3 through a form. I created a query that contains a "studentaverage" which I put in the formula ([sc1]+[sc2]+[sc3])/3 to get the average. Then I have a report that pulls that average sorts it by category, then lists the average for each student in that category in descending order so we know who the winners are. That was the easy part. Now for the hard part. I can't remember how I got the "average of averages". This report needs to take the average of each of the students for each school, not project category. Not every school has the same number of participants, so I need to help remembering how to make an average of averages that may have 6 kids from one school, 10 from another, 8 from another, etc, so that they can be ranked to see which school had the highest average and award trophies to the top 3 schools.

Can anyone help? I am not microsoft office guru, I am just fireman who was trying to help out his then wife's school when the original programmer decided to market his product after using the school system for years to develop it. I am attaching a copy of the database. I am sure it breaks a number of rules of the trade and may not be the most efficient, but it was the best I could do with no experience, a bunch of books and a deadline. There are some items that are left from the old database that I haven't deleted yet. The items that include "2013" are the new creations, with the exception of the "student registration" table that has some sample data in it to play with.

Thanks!

Herb

Apparently it is too big to upload? If you would like to take a look, here is a link to it on my google drive:

https://docs.google.com/file/d/0B0Zv...it?usp=sharing

Thanks again!

Herb

[June7]

2mb zip file allowed as attachment to post. If you had run Compact & Repair then zipped the file, it could have been uploaded as post attachment. After C&R the file is only 780kb and zip file is only 93kb.

Calculating aggregates (sum, average, max, etc) of fields is a Totals (GROUP BY) query. This can be done in query or use a report to group and sort records and do aggregate calcs in footer sections. The report option will allow display of detail data as well as calculate summary information.

Refer to Access Help on building Totals query and on designing reports using Grouping & Sorting with aggregate calcs. I see that you have Totals queries and grouping reports so a little review should bring this all back to you, like riding a bicycle.