Thank you, Duane Hookom, for your reply. I can see why they call you guys
MVP! With help from you and others like you, I've been able to solve some
pretty sticky problems.
I tried your suggestion. With the tblLetterGrades permanently joined
[studentGrades].[letterGrade] M:1 [letterGrades].[letterGrade] I got zero
records. But when I removed the join in the query builder window, it
worked, but it multiplied the records each letter grade. So I tried
grouping it as you said and I got the following error:
"You tried to execute an expression that does not include the specified
expression '[lname]&", "&[fname] as part of an aggregate function"
Here's the query I tried to run:
SELECT tblLetterGrades.letterGrade, courses.courseCode, [lName] & ", " &
[fname] AS [Student Name], activities.activityDescription,
activities.activityOrder,
[activities].[activityWeight]/[qrySumOfActivityWeightsInGroups].[sum of
activity weights] AS [Percentage Weight], groups.groupWeight,
studentScores.score,
(([activities].[activityWeight]/[qrySumOfActivityWeightsInGroups].[sum of
activity weights])*[groups].[groupWeight]*[studentScores].[score]/100) AS
ActivityPoints
FROM tblLetterGrades, students INNER JOIN ((groups INNER JOIN (courses INNER
JOIN qrySumOfActivityWeightsInGroups ON courses.courseCode =
qrySumOfActivityWeightsInGroups.courseCode) ON (courses.courseCode =
groups.courseCode) AND (groups.groupID =
qrySumOfActivityWeightsInGroups.groupID)) INNER JOIN (activities INNER JOIN
studentScores ON activities.activityID = studentScores.activityID) ON
groups.groupID = activities.groupID) ON students.studentID =
studentScores.studentID
GROUP BY courses.courseCode, tblLetterGrades.letterGrade
HAVING (((studentScores.score) Between [tblLetterGrades].[minScore] And
[tblLetterGrades].[maxScore]))
ORDER BY courses.courseCode, [lName] & ", " & [fname],
activities.activityOrder, groups.groupOrder;
Maybe I'll just write a module that populates a temporary table with the
correct data, runs the report, then removes the data from the same temporary
table. Maybe that's unsophisticated, but at least I can do it in small
steps. What do you think?
Duane Hookom said:
If you are in college, you should probably do this right by creating a table
of score ranges.
tblScoreRanges
====================
MinScore MaxScore ScoreTitle
0 62 Failing
63 72 Not too good
73 82 About Average
83 92 Not too bad
93 100 Acceptable
You can then add this table to your query and set the criteria under the
Score field to
Between [MinScore] AND [MaxScore]
This assumes integer values for scores.
Then group by ScoreTitle and count the number of scores.
--
Duane Hookom
MS Access MVP
Richard Hollenbeck said:
I need help thinking this one through. I need to write a report that
displays the number of students in a course getting below 62, between 63-72,
between 73-82, between 83-92, and above 93. I tried just putting a WHERE
clause in the text box like this: =[CountOfScore] WHERE ([sumOfScore]<62);
etc. but that didn't work. It said, "The expression you entered contains
invalid syntax. You may have entered an operand without an operator."
Maybe there's something wrong with my saved query:
SELECT qryActivityPercentageWeights.courseCode,
qryActivityPercentageWeights.courseDescription,
Sum(qryActivityPercentageWeights.score) AS SumOfscore,
Count(qryActivityPercentageWeights.score) AS CountOfscore
FROM qryActivityPercentageWeights
GROUP BY qryActivityPercentageWeights.courseCode,
qryActivityPercentageWeights.courseDescription;
Any ideas? Many Thanks!
Rich Hollenbeck
