Problem limiting records based on unrelated field

[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

 

[Ted Allen]

Hi Richard,

I can think of two things offhand.

You could base your DSum() function on a query that
contains both tables, properly joined, (rather than
the "groups" table) so that it can see both fields that
you need in your criteria expression.

You could use a subquery to calculate the value.

Unfortunately, I just got a rush assignment so I have to
run, but post back if you want help on the subquery (post
the table join fields as well) and I will take a look at
it when I get a chance.

-Ted Allen

-----Original Message-----
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


.

 

[Richard Hollenbeck]

Thanks, Ted. I'll read up on subqueries and see what I need to do. One
problem I can see is that the activityWeight is on the "Many" side (if I'm
saying this right.) I'm checking to see if there are any activities in a
group with any weight assigned to them; otherwise exclude that group without
activities from the equation. This morning I had several groups with
activities, Essays and Participation for example. But the Exams group had
no activities (i.e. mid-term, final, etc.). Yet the Exams group had weight.
So the program was calculating the total grade based on some empty group and
giving lower grades to each student in the course than they should get. As
a temporary work-around, I deleted the Exams group from the table "groups"
and everything went fine--that is, all the grades were correct. But, as I
said, it has to be a temporary solution. Instead of deleting empty groups
that I'll eventually have to put back, I need to simply check for empty
values, or lack of values in the activities table related to that group.

I think you're right about the sub-query. I think that will do the trick.
Now I just need to learn how to do it. I'll go back to the chapter on SQL
to see what they say about subqueries then I'll post my proposal (or my
solution or my next question.) Thanks so much for your help.

Rich

Hi Richard,

I can think of two things offhand.

You could base your DSum() function on a query that
contains both tables, properly joined, (rather than
the "groups" table) so that it can see both fields that
you need in your criteria expression.

You could use a subquery to calculate the value.

Unfortunately, I just got a rush assignment so I have to
run, but post back if you want help on the subquery (post
the table join fields as well) and I will take a look at
it when I get a chance.

-Ted Allen

-----Original Message-----
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


.

 

[Ted Allen]

Hi Richard,

There may be another alternative to the subquery (or
actually just a different method of using subqueries),
depending on what you are doing with your main query. If
you only want to see records in your main query for
groups that have weights assigned to them, you could use
a more simple subquery as the criteria for the GroupID.
Something like:

In (SELECT Activities.groupID FROM Activities WHERE
Activities.activityWeight > 0)

But, if you want your main query to return records for
groups that do not yet have any activity weights
assigned, I believe that you can use a correlated
subquery to do your calculation. If I understand your
situation correctly, you wanted to modify:

DSum("groupweight","groups","courseCode= '" & [courses].
[courseCode] & "'")

to further restrict the domain to only count those groups
with a sum activity weight greater than 0. If that is
the case, the following may work:

(SELECT Sum(groupsub.groupweight) FROM groups AS groupsub
INNER JOIN activities AS activitiesub ON groupsub.groupID
= activitiesub.groupID WHERE groupsub.courseCode =
groups.courseCode AND activitiesub.activityWeight > 0)

From reading your last post though, I'm not completely
sure that this is actually what you are looking for, but
I thought I would provide it to give you an idea of how
subqueries can be used.

By the way, the subquery above (the second one) is called
a correlated subquery because part of the condition comes
from the main query. When using correlated subqueries,
it is necessary to give aliases to any table names that
are already included in the main query so that they do
not get confused. Often, VT is used as the alias,
standing for Virtual Table. In your case I added "ub" to
the end of each table name so that they would end
in "sub" to make it easy to remember that they are the
subquery tables.

You can find a lot of info on subqueries, and sample
applications, if you search google using the link below
for "correlated subqueries":

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-
8&group=microsoft.public.access

Microsoft access also has a help page on subqueries in
the SQL reference (at least in A2002), and it is helpful,
but it is so short that most of us don't start to
discover the full usefullness of subqueries until seeing
the various applications in other areas such as this
group.

A few basic guidelines for subqueries that I can think of
offhand:
- They are always enclosed in ()'s
- They can be used as field criteria, or as a field value.
- They generally should only return one field and one
record (thus they usually use some type of aggregate
function)
- They can return one field with multiple records in some
cases, such as when used as criteria and combined
with "In" as in the first case above (could also use
with "Not In").
- A tool that is often used to develop subqueries is to
create the query on its own in the query builder, copy
the SQL, paste it in the main query and modify as needed.

Hopefully that will help. If you have a specific
subquery that you would like help with, post what you
would like to do and I'll help if I can.

-Ted Allen

-----Original Message-----
Thanks, Ted. I'll read up on subqueries and see what I need to do. One
problem I can see is that the activityWeight is on the "Many" side (if I'm
saying this right.) I'm checking to see if there are any activities in a
group with any weight assigned to them; otherwise exclude that group without
activities from the equation. This morning I had several groups with
activities, Essays and Participation for example. But the Exams group had
no activities (i.e. mid-term, final, etc.). Yet the Exams group had weight.
So the program was calculating the total grade based on some empty group and
giving lower grades to each student in the course than they should get. As
a temporary work-around, I deleted the Exams group from the table "groups"
and everything went fine--that is, all the grades were correct. But, as I
said, it has to be a temporary solution. Instead of deleting empty groups
that I'll eventually have to put back, I need to simply check for empty
values, or lack of values in the activities table related to that group.

I think you're right about the sub-query. I think that will do the trick.
Now I just need to learn how to do it. I'll go back to the chapter on SQL
to see what they say about subqueries then I'll post my proposal (or my
solution or my next question.) Thanks so much for your help.

Rich

Hi Richard,

I can think of two things offhand.

You could base your DSum() function on a query that
contains both tables, properly joined, (rather than
the "groups" table) so that it can see both fields that
you need in your criteria expression.

You could use a subquery to calculate the value.

Unfortunately, I just got a rush assignment so I have to
run, but post back if you want help on the subquery (post
the table join fields as well) and I will take a look at
it when I get a chance.

-Ted Allen

-----Original Message-----
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


.

.