Report Average Calculation

[Zipdoc]

Good morning, my first Access post, so I'm a bit new at this.

I'm using a database to keep track of faculty assessments and ratings. The table has a listing for faculty name, academic year, course, questions 1-9 (scorred numerically from 1-5) and a comment field.

I've built 2 queries, one to drive the majority of the reporting (qryFacAssessment), another to simply to calculate the averages for all 9 questions (qryFacAssessMean).

The report works well and takes the numerical information of each faculty member and compares it to the average. What I would like to do is further break this down by course. I can sort the qryFacAssessMean by course, and the query gives me the correct averages per question per course. My problem is that I can't seem to figure out how to get that specific information over to the report, and then calculate a the average for the total of all courses. I have tried using DAvg, and only get an error message.

Any help in the right direction would be most appreciated.

Many thanks in advance.

Zipdoc

 

[Konai]

We seem to be having a similar problem. If anyone can answer this I'll post the solution on my own as well.

 

[Konai]

It seems like calculations like this can be better achieved inside of queries.

http://www.access-programmers.co.uk/forums/showthread.php?p=918591#post918591

If you could post what you have so far it would help us find out how to help you get it working. That example above only show a simple division calculation between those 2 fields. You could just use the simple math to do what you are looking for.

If you had one query to count how every many items there are to be averaged.

[query1]![count_of_items]

One query to total them all up.

[query2]![total_of_items]

then you could make a third query to get the average and place that on the report.

In design view on query 3 you could place something like this
My_Average: [query2]![total_of_items]/[query1]![count_of_items]*100

I'm sure there is an easier way than this though.

Please post what version of access you are using too. I forgot to do that myself on the first post I made.

 

[Galaxiom]

I don't really understand the question very well.

However note that any field in the Record Source of a report or form can be totalled in a textbox using a Control Source of this nature.

=Sum([fieldname])
=Avg([fieldname])


Other queries can be used as Record Sources in subreports and totalled as above and then that textbox referred to from a textbox on the main form with the ControlSource:

=[subformcontrolname].[textboxname]

This is particularly useful when the subform is in Continuous or Datasheet mode since the textboxes in the subform header or footer are not visible.

 

[Zipdoc]

Excellent! Thank you both. I have recently started using Access 2007. My real problem was trying to represent two datasources within the same report, and had not really explored using a subreport. If I put the subreport in the footer, as Galaxion has suggested, I can set the visible property to "No".

I made another query to sort the question averages by section (qryFacAssessMeansBySection). I added a subreport to the my original report footer, using the new query as the data source. The report then prints the faculty's score, the overall average, and then I am able to add a textbox that pulls the section average from the new subreport.

Doesn't seem very "neat", but it certainly works. I just need play with the formatting...

Thanks again.

 

[Konai]

That is good to know.
I had tried using sub reports as well, but it seemed to clunky to me.
If I can't figure out how to get it done outside of the report I'll eventually
have to do the same .

If I get it figured out though I'll be sure to post it here.
Thanks for the info Galaxiom!

EDIT

Well I found another way to get multiple query results into one query (even though there is no link between them)
and then get them all in one row.

http://www.access-programmers.co.uk/forums/showthread.php?p=918825#post918825

I'm not sure if this is the same thing, but I hope it is helpful to someone here.