R
Richard Hollenbeck
Many thanks. . .
In the following query, I need to limit the records used to calculate the
artificial field "GroupPercentageWeight" to groups that have
"[activities].[activitiyWeight] > 0." The problem is that activities is a
separate but related table. In the part of the following query, the part
that says,
"[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '"
& [courses].[courseCode]
& "'") AS GroupPercentageWeight"
I need to add the criteria, "AND activities.activityWeight > 0 " but when I
try to add this to the DSum() criteria I get an empty set in some of the
other columns where they would otherwise be populated. If I try to put it
in a WHERE or HAVING clause I get an error because it's supposed to be an
aggregate. Although activities is related to the groups table, it is not
the table being worked on--groups is the table being worked on, not
activities.
Here's my question:
How can I get this extra criteria into this query?
Here's the entire SQL statement:
SELECT [students].[studentID], [courses].[courseCode], [groups].[groupID],
[activities].[activityID], [groups].[groupWeight], [groups].[groupOrder],
[activities].[activityWeight], [activities].[activityOrder],
[studentScores].[score], [students].[lname] & ", " & [students].[fname] AS
Student,
[activities].[activityWeight]/DSum("activityweight","activities","groupID="
& [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS activityPoints
FROM [students] INNER JOIN (((courses INNER JOIN [groups] ON
[courses].[courseCode] = [groups].[courseCode]) INNER JOIN
[studentsInCourses] ON [courses].[courseCode] =
[studentsInCourses].[courseCode]) INNER JOIN ([activities] INNER JOIN
[studentScores] ON [activities].[activityID] = [studentScores].[activityID])
ON [groups].[groupID] = [activities].[groupID]) ON ([students].[studentID] =
[studentScores].[studentID]) AND ([students].[studentID] =
[studentsInCourses].[studentID])
GROUP BY [students].[studentID], [courses].[courseCode], [groups].[groupID],
[activities].[activityID], [groups].[groupWeight], [groups].[groupOrder],
[activities].[activityWeight], [activities].[activityOrder],
[studentScores].[score], [students].[lname] & ", " & [students].[fname];
This is not as confusing as it used to be. Maybe I'm just getting used to
SQL. But this criteria is driving me bonkers because I can't figure out
where to put it.
Thanks a bunch!
Rich Hollenbeck
In the following query, I need to limit the records used to calculate the
artificial field "GroupPercentageWeight" to groups that have
"[activities].[activitiyWeight] > 0." The problem is that activities is a
separate but related table. In the part of the following query, the part
that says,
"[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '"
& [courses].[courseCode]
& "'") AS GroupPercentageWeight"
I need to add the criteria, "AND activities.activityWeight > 0 " but when I
try to add this to the DSum() criteria I get an empty set in some of the
other columns where they would otherwise be populated. If I try to put it
in a WHERE or HAVING clause I get an error because it's supposed to be an
aggregate. Although activities is related to the groups table, it is not
the table being worked on--groups is the table being worked on, not
activities.
Here's my question:
How can I get this extra criteria into this query?
Here's the entire SQL statement:
SELECT [students].[studentID], [courses].[courseCode], [groups].[groupID],
[activities].[activityID], [groups].[groupWeight], [groups].[groupOrder],
[activities].[activityWeight], [activities].[activityOrder],
[studentScores].[score], [students].[lname] & ", " & [students].[fname] AS
Student,
[activities].[activityWeight]/DSum("activityweight","activities","groupID="
& [groups].[groupID]) AS ActivityPercentageWeight,
[groups].[groupWeight]/DSum("groupweight","groups","courseCode= '" &
[courses].[courseCode] & "'") AS GroupPercentageWeight,
[score]*[ActivityPercentageWeight]*[GroupPercentageWeight] AS activityPoints
FROM [students] INNER JOIN (((courses INNER JOIN [groups] ON
[courses].[courseCode] = [groups].[courseCode]) INNER JOIN
[studentsInCourses] ON [courses].[courseCode] =
[studentsInCourses].[courseCode]) INNER JOIN ([activities] INNER JOIN
[studentScores] ON [activities].[activityID] = [studentScores].[activityID])
ON [groups].[groupID] = [activities].[groupID]) ON ([students].[studentID] =
[studentScores].[studentID]) AND ([students].[studentID] =
[studentsInCourses].[studentID])
GROUP BY [students].[studentID], [courses].[courseCode], [groups].[groupID],
[activities].[activityID], [groups].[groupWeight], [groups].[groupOrder],
[activities].[activityWeight], [activities].[activityOrder],
[studentScores].[score], [students].[lname] & ", " & [students].[fname];
This is not as confusing as it used to be. Maybe I'm just getting used to
SQL. But this criteria is driving me bonkers because I can't figure out
where to put it.
Thanks a bunch!
Rich Hollenbeck