Trying to add two more peices of information to an already too-complicated query.

[Richard Hollenbeck]

I have a query that is already way too complicated and also contains another
saved query which is itself rather complicated. Now I need two more items
of information for a report which would cause a circular reference. Can't I
just make a new query and use it in the report even though the report is
already based on the query I'm talking about? Here is the first query and
the query that is based on it:

This one's called "qrySumOfActivityWeightsInGroups":

SELECT courses.courseCode, groups.groupOrder, groups.groupID,
Sum(activities.activityWeight) AS [sum of activity weights],
groups.GroupDescription
FROM (courses INNER JOIN groups ON courses.courseCode = groups.courseCode)
INNER JOIN activities ON groups.groupID = activities.groupID
GROUP BY courses.courseCode, groups.groupOrder, groups.groupID,
groups.GroupDescription
ORDER BY courses.courseCode, groups.groupOrder;


The next one uses "qrySumOfActivityWeightsInGroups" it is called
"qryActivityPercentageWeights":

SELECT qrySumOfActivityWeightsInGroups.*, activities.activityWeight,
activities.activityWeight/qrySumOfActivityWeightsInGroups.[sum of activity
weights] AS [Percentage Weight], studentScores.score,
studentScores.studentID, groups.groupWeight,
(studentScores.score*(activities.activityWeight/qrySumOfActivityWeightsInGro
ups.[sum of activity weights])*groups.groupWeight)/100 AS points,
students.lName & ", " & students.fname AS [Student Name],
courses.courseDescription, activities.activityOrder,
activities.activityDescription
FROM students INNER JOIN ((courses INNER JOIN (groups INNER JOIN
(qrySumOfActivityWeightsInGroups INNER JOIN activities ON
qrySumOfActivityWeightsInGroups.groupID = activities.groupID) ON
(groups.groupID = activities.groupID) AND (groups.groupID =
qrySumOfActivityWeightsInGroups.groupID)) ON (courses.courseCode =
groups.courseCode) AND (courses.courseCode =
qrySumOfActivityWeightsInGroups.courseCode)) INNER JOIN studentScores ON
activities.activityID = studentScores.activityID) ON students.studentID =
studentScores.studentID;


All I need to do is represent a total amount of points earned by each
student within each group and then the total for the course (semester
grade). I can't figure out what to add to do that without screwing up these
queries. It's already a big mess!

I like the other idea: writing another little query and somehow referencing
it in the report. By the way, this report is called "Student Summary." It
displays each activity (like a particular essay or exam) by groups (like
"essays" and "exams"). Then it displays the weight for each activity and
each group. Then it displays the scores earned by each student in each
activity and calculates those scores, by the activity and group weights,
into "points."

I want the total number of points in each group, and then the total number
of points earned by each student in the course.

Oh! What a headache!!! Anybody feel like taking on this one?

Rich Hollenbeck

 

[Richard Hollenbeck]

Sorry for the multiple posts of this. It was an error. I couldn't send it
after several attempts. Then I changed the default mail and news handler
from Mozilla Thunderbird to Outlook Express and I found about five repeats.
It wasn't done purposely.