Aggregate Queries hassles

[Richard Hollenbeck]

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified expression
'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(a
ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students in a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.

 

[hermie]

Hi Richard

You did not received my file in the email?
In this file I have a module that calculates the A,B,C,D and F

Herman

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified expression'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(a
ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students in a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.

 

[hermie]

Here is a example

Public Function caltarea(Pertar As String) As String

Select Case CDbl(Pertar)
Case Is >= 92#
caltarea = "A"
Case Is >= 82# And Pertar < 92#
caltarea = "B"
Case Is >= 72# And Pertar < 82#
caltarea = "C"
Case Is >= 62# And Pertar < 72#
caltarea = "D"
Case Is < 72#
caltarea = "F"
Case Else
caltarea = "?"
End Select
End Function

Herman

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified expression'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(a
ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students in a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.

 

[Tom Ellison]

Dear Richard:

In order to try to make some progress on this question, I think we
must deflect away from a direct answer to the question you posed and
begin with something more fundamental.

The first area I see that needs addressing is seen in your FROM
clause. This lists 3 tables:

groups
activities
studentScores

but does so without any kind of relationship or "JOIN" taking place.
The result would be a "cross product" of the rows in these 3 tables,
as though there were no relationship in the way these tables are
designed.

I can't think of what you have stored in these tables. Is there
anything in your design that specifies which activity is associated
with a score or group? Or does every activity apply to each and every
score and group? This latter question, if answered affirmative,
represents how your query is currently constructed. I guess I find
that to be an unlikely intention.

Some detail of these tables might lend light to this issue. Perhaps
some samples of the data for, say, one student, could be most helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Richard Hollenbeck]

No I didn't get it. If you try again I'll check my mail later this
afternoon.
Rich

Hi Richard

You did not received my file in the email?
In this file I have a module that calculates the A,B,C,D and F

Herman

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified expression

'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(a

ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students

in

a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.

 

[Richard Hollenbeck]

Hermie,

Thanks for the effort. I know what to do with the final numbers, but I need
to get the numbers first, then count the students with each grade. For
example, if the group weight for Essays is 15% of the entire course, and one
essay's activity weight is 5% of the Essays group, and there are a total of
90% of group weights filled in the Essays group, and 45% of the activity
weights are filled, if a student gets 82% on said essay, then that essay's
portion of the entire semester grade would be (15/90) * (5/45) * 0.82 or
(16.67) * (0.11) * 0.82 = 0.0151 Add all the little numbers (like this
0.015) and you will have the entire semester grade for that student. Now I
already have a report that properly displays all the activities in groups
with their weights and scores and the final grade at the bottom of the
report. But I need to further calculate how many of these students got an
A, a B, a C, a D, and an F. I will make a report with a bar graph showing
the grade distribution for the entire class.

Thanks.
Rich

Here is a example

Public Function caltarea(Pertar As String) As String

Select Case CDbl(Pertar)
Case Is >= 92#
caltarea = "A"
Case Is >= 82# And Pertar < 92#
caltarea = "B"
Case Is >= 72# And Pertar < 82#
caltarea = "C"
Case Is >= 62# And Pertar < 72#
caltarea = "D"
Case Is < 72#
caltarea = "F"
Case Else
caltarea = "?"
End Select
End Function

Herman

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified expression

'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(a

ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students

in

a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.

 

[Richard Hollenbeck]

Okay, I'll try using the Query By Example tool first. That will build the
joins quickly. The way I've done it didn't work, but at least you will see
what I'm trying to do. I'll also describe the relationships, etc. Then
I'll post it. I'll need at least an hour to get this ready, but that's a
small amount of time compared with the three long days I've already spent on
this problem. So you can expect another post in this thread from me in
about an hour or so. Thanks Tom.

Rich Hollenbeck

Dear Richard:

In order to try to make some progress on this question, I think we
must deflect away from a direct answer to the question you posed and
begin with something more fundamental.

The first area I see that needs addressing is seen in your FROM
clause. This lists 3 tables:

groups
activities
studentScores

but does so without any kind of relationship or "JOIN" taking place.
The result would be a "cross product" of the rows in these 3 tables,
as though there were no relationship in the way these tables are
designed.

I can't think of what you have stored in these tables. Is there
anything in your design that specifies which activity is associated
with a score or group? Or does every activity apply to each and every
score and group? This latter question, if answered affirmative,
represents how your query is currently constructed. I guess I find
that to be an unlikely intention.

Some detail of these tables might lend light to this issue. Perhaps
some samples of the data for, say, one student, could be most helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified

expression

'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(

a

ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students in a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.

 

[Richard Hollenbeck]

Tom,

I just went to the QBE to quickly build something like what I need, but, of
course, it won't work. But it should present an idea what I'm trying to
accomplish. The parentheses do not appear to show the order of operations
on the division and multiplication I need, as I typed it in the QBE. When I
went to SQL view it did it the way it "wanted" to.

SELECT courses.courseCode, studentsInCourses.studentID,
Sum([groups].[groupID]/Sum([groups].[groupID])*[activities].[activityID]/Sum
([activities].[activityID])*[studentScores].[score]) AS [Semester Grade]
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 =
studentsInCourses.studentID) AND (students.studentID =
studentScores.studentID)
GROUP BY courses.courseCode, studentsInCourses.studentID
ORDER BY courses.courseCode, studentsInCourses.studentID;

One thing is that it doesn't seem to like any kind of sum(...sum(...))
construction (summing sums, etc.)

It also doesn't like the extra fields, courses.courseCode and
students.studentID. I think I need the fields.

As far as relationships go, A picture tells a thousand words.
Unfortunately, we can't post images. But I have another solution: I
uploaded an image of my relationships to
http://members.fortunecity.com/hollenbeck/grades/relationships.html. Also,
that will better explain what I'm up to. Every student is connected to a
course by a composite key with courses in a sort of bridge table called
StudentsInCourses (for want of a better name.) In the same way, student
scores are connected to students and activities in a StudentScores table.
courses are connected to groups which are connected to activities. It sort
of forms a complete circle, but I don't think I have circular relationships
because they are pointing different directions. You'll see what I mean if
you click on the site.

Thank you very much
Rich Hollenbeck

Dear Richard:

In order to try to make some progress on this question, I think we
must deflect away from a direct answer to the question you posed and
begin with something more fundamental.

The first area I see that needs addressing is seen in your FROM
clause. This lists 3 tables:

groups
activities
studentScores

but does so without any kind of relationship or "JOIN" taking place.
The result would be a "cross product" of the rows in these 3 tables,
as though there were no relationship in the way these tables are
designed.

I can't think of what you have stored in these tables. Is there
anything in your design that specifies which activity is associated
with a score or group? Or does every activity apply to each and every
score and group? This latter question, if answered affirmative,
represents how your query is currently constructed. I guess I find
that to be an unlikely intention.

Some detail of these tables might lend light to this issue. Perhaps
some samples of the data for, say, one student, could be most helpful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

First: I want to thank you guys very much. I've learned much this
newsgroup. I'm still having a tough time with aggregate queries.

Next the code:
SELECT sum((groups.groupWeight/Sum(groups.groupWeight)) *
(activities.activityWeight / Sum(activities.activityWeight)) *
studentScores.score) AS [WeightedAverage] FROM groups, activities,
studentScores GROUP BY studentScores.studentID;

Now the error:
"You tried to execute a query that does not include the specified

expression

'groups.groupWeight/Sum(groups.groupWeight)*(activities.activityWeight/Sum(

a

ctivities.activityWeight))*studentScores.score' as part of an aggregate
function."

the sum of all the scores times the group weight times the activity weight
for one student will be that student's grade for the semester. But each
group's weight needs to first be divided by the sum of all the group weights
in the course and the activity weight needs to be divided by the sum of all
the activity weights in that group.

This query isn't even finished. I need to determine how many students in a
course got an A, B, C, D, or F for a report about the course stats:

A >= 92
B >= 82<92
C >= 72<82
D >= 62<72
F < 62

I think I could figure out the logic of how to do this (MAYBE,) but I'm
having trouble getting the aggregate query to cooperate with me. I don't
like how this works, probably because I don't fully understand how this kind
of query works.