Sorting by Average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a survey type db that weights various questions. One question is overall grade. I then have to publish a report showing the averages of each question. So I am grouping and only showing the group header and footer. How can I now sort the report by the average of of the overall score.

Thanks for any help.....
 
You can create a query that has only the OverAllScore and person or whatever
you want to assign the OverAllScore to. Add this query to your report's
record source and join the significant fields. You can then add the
OverAllScore to the sorting and grouping.

--
Duane Hookom
MS Access MVP


Dmiller4444 said:
I have a survey type db that weights various questions. One question is
Click to expand...
overall grade. I then have to publish a report showing the averages of each
question. So I am grouping and only showing the group header and footer. How
can I now sort the report by the average of of the overall score.
 
Hi

Again thanks for your earlier reply. Try as I may, I can't seem to figure out how to use your suggestion. Let me explain more. This is the concept of my report
Selection clarity Writing Applicability Overal
Question1 4 3 3
Question1 2 2 3
Question1 4 4 4
Avg 3.3 3 3.3

Question2 2 2 2
Question2 3 3 3
Avg 1.7 1.7 1.7 1.

And so on. There are say 50 questions, and 20 responses, and I want to sort the report based on the avg score for overall

Hope that makes sense. thx again!
 
Apparently you have fields [clarity], [Writing],... which is a bit
un-normalized. A better structure would take your current field names and
make them data values. Your display below would be generated from 15 records
rather than 5 (unless your display doesn't depict your structure).

I would first create a union query to normalize:
SELECT Selection, "Clarity" as Attribute, [Clarity] as Response
FROM tblSurvey
UNION ALL
SELECT Selection, "Writing", [Writing]
FROM tblSurvey
UNION ALL
....etc..;

Then, you can create a totals query
SELECT Selection, Avg(Response) as AvgResp
FROM quniUnionQuery
GROUP BY Selection;

You can then add this query to your report's record source.

--
Duane Hookom
MS Access MVP


dmiller4444 said:
Hi,

Again thanks for your earlier reply. Try as I may, I can't seem to figure
Click to expand...
out how to use your suggestion. Let me explain more. This is the concept of
my report.
Selection clarity Writing Applicability Overall
Question1 4 3 3 3
Question1 2 2 3 2
Question1 4 4 4 4
Avg 3.3 3 3.3 3

Question2 2 2 2 2
Question2 3 3 3 3
Avg 1.7 1.7 1.7 1.7

And so on. There are say 50 questions, and 20 responses, and I want to
Click to expand...
sort the report based on the avg score for overall.
 
Sorry, I should not have put the word selection there. I now see that that is confusing. It should look like this
 
Apparently you have fields [clarity], [Writing],... which is a bit
un-normalized. A better structure would take your current field names and
make them data values. Your display below would be generated from 15 records
rather than 5 (unless your display doesn't depict your structure).

I would first create a union query to normalize:
SELECT Submission, "Clarity" as Attribute, [Clarity] as Response
FROM tblSurvey
UNION ALL
SELECT Submission, "Writing", [Writing]
FROM tblSurvey
UNION ALL
....etc..;

Then, you can create a totals query
SELECT Submission, Avg(Response) as AvgResp
FROM quniUnionQuery
GROUP BY Submission;

You can then add this query to your report's record source.

--
Duane Hookom
MS Access MVP


dmiller444 said:
Sorry, I should not have put the word selection there. I now see that that
Click to expand...
is confusing. It should look like this:
 
Back
Top