In query records returned multiple times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Access 2003. Trying to make a grading report that groups on fldyear,
fldquarter, fldSubject, fldLN/FN (trimmed field) and fldGradeWeight then I
want to set up my report with column one listing each individual assignment
with the second column showing the grade for each assignment. fldGradeWeight
will have a group footer which shows the average for all grades falling in
this weight category (this comes from qryAvgGrades). In the page footer, I
want the final grade which comes from qrySumOfAvgGrades.
I am messing up with the query that I am trying to make to base this report
on. All is well when I make a query from qryAvgGrades, qrySumOfAvgGrades, and
tblInfo (this table contains the student ID, names, addresses, etc.) But when
I try to add tblMainTable which is where all the individual assignments and
grades are, the query returns more than one row for a single record, e.g.,
Quiz 2 more than one time when there was only one Quiz 2 in the tblMainTable.
Same with all the assignments.
I searched this newsgroup and think I have a relationship problem but
couldn't make anything work. My query is based on these tables/queries:
tblInfo (names and addresses of students), tblMain (where the assignments,
weights, grades, etc. are recorded), qryAvgGrade (averages grades by weight),
qrySumOfAvgGrades (sums the averaged weight categories for the final grade).
All relationships are on the ID field.
Thanks in advance for your help.
 
Serendipity said:
Using Access 2003. Trying to make a grading report that groups on fldyear,
fldquarter, fldSubject, fldLN/FN (trimmed field) and fldGradeWeight then I
want to set up my report with column one listing each individual assignment
with the second column showing the grade for each assignment. fldGradeWeight
will have a group footer which shows the average for all grades falling in
this weight category (this comes from qryAvgGrades). In the page footer, I
want the final grade which comes from qrySumOfAvgGrades.
I am messing up with the query that I am trying to make to base this report
on. All is well when I make a query from qryAvgGrades, qrySumOfAvgGrades, and
tblInfo (this table contains the student ID, names, addresses, etc.) But when
I try to add tblMainTable which is where all the individual assignments and
grades are, the query returns more than one row for a single record, e.g.,
Quiz 2 more than one time when there was only one Quiz 2 in the tblMainTable.
Same with all the assignments.
I searched this newsgroup and think I have a relationship problem but
couldn't make anything work. My query is based on these tables/queries:
tblInfo (names and addresses of students), tblMain (where the assignments,
weights, grades, etc. are recorded), qryAvgGrade (averages grades by weight),
qrySumOfAvgGrades (sums the averaged weight categories for the final grade).
All relationships are on the ID field.
Thanks in advance for your help.
Click to expand...

If you are joining the tables/queries it's likely there is more than one
row in one table matching a row in another table in the field(s) you are
joining.

E.g., you say there is only (1) Quiz 2 in the main table. If you join
this to qrySumOfAvgGrades on Quiz# only, and Quiz 2 appears ten times in
qrySumOfAvgGrades, 10 rows will be returned.

You may need additional joins to constrain the matches but it's hard to
say how without seeing your query.

Also, tblMain might be a problem in terms of normalization. If it
describes assignments, that's fine, but grades belong to students and
assignments, so they might should be in a separate table. You may want
to take a look at this PDF to see how students, assignments, and grades
could be modeled:

http://vfdrake.home.comcast.net/files/students.pdf

HTH
 
The PDF was a wonderful help! Could you please clarify a couple of things. We
are a Kindergarten through 8th grade school, so do we need Courses? In our
situation would Courses be Kindergarten and then Classes would be science,
reading, etc. Or would Kindergarten be another table up on the level of Class
Years and Semesters. I am assuming that Class Years is for example 2006-2007?
Thank you so much for your help.
 
Serendipity said:
The PDF was a wonderful help! Could you please clarify a couple of things. We
are a Kindergarten through 8th grade school, so do we need Courses? In our
situation would Courses be Kindergarten and then Classes would be science,
reading, etc. Or would Kindergarten be another table up on the level of Class
Years and Semesters. I am assuming that Class Years is for example 2006-2007?
Thank you so much for your help.
Click to expand...


Glad to know you found that useful. I created it in response to someone
looking for a much more detailed model and I'm sure it's not perfect,
but it was a good exercise for me to conceptualize the relationships.

Part of the concept in the diagram was to relate "classes" (like
reading, writing, and arithmetic) to "courses", akin to major areas of
study. I was not attempting to determine or nominate students into a
grade level -- this would be a calculated measure based on credits
attained and almost certainly additional, more complicated rules.
[Indeed, I now see the underlying quantity is conspicuously absent from
my model (^: Like I said, it surely isn't perfect...]

In your case the "courses" table as given might very well serve as
"grade level" (i.e., Kindergarten, etc.) since each level probably has
specific required "classes", and perhaps you do not calculate credits to
determine a student's grade level.

"ClassYears" is there to conveniently aggregate classes over time. Same
with "Semesters". You may not need these, but then again they may be
useful to time-stamp classes.

I was trying to steer to the relationships between Students,
Assignments, and Grades, and realize the many-to-many relationship
between students and assignments. Looks like you have given this some
thought!

BTW in the diagram, an arrow pointing from A to B means "A is a member
of B", or "A belongs to B". Hence, "grades belong to students" and
"classes are members of courses".

Conversely, an arrow pointing towards B away from A reads "B has/have
A". Hence, "assignments have grades" and "classes have assignments".

Best Regards,
 
This is beginning to make sense. Thanks for your help.

In some of the tables, I added another field for description because I
thought that it would make data entry easier, e.g, in the AssignTypes table
the Id is FinDra and the description is Writing--Final Draft

Is the Assignments table to entered the assignments that the students have
completed or to make a list of all possible assignments, e.g. to use for
lookup or a query.

Also, in my sanerio, all assignment types don't always get the same weight,
e.g., I may give a test worth 30% but then give it again for 10% but the
assignment type is still "test," so would I put AssignmentWeight in the
assignments table?
Where in your model do the grades get associated with the specific
assignments?

At least now I know what questions to ask!

Thanks



Smartin said:
Serendipity said:
The PDF was a wonderful help! Could you please clarify a couple of things. We
are a Kindergarten through 8th grade school, so do we need Courses? In our
situation would Courses be Kindergarten and then Classes would be science,
reading, etc. Or would Kindergarten be another table up on the level of Class
Years and Semesters. I am assuming that Class Years is for example 2006-2007?
Thank you so much for your help.
Click to expand...


Glad to know you found that useful. I created it in response to someone
looking for a much more detailed model and I'm sure it's not perfect,
but it was a good exercise for me to conceptualize the relationships.

Part of the concept in the diagram was to relate "classes" (like
reading, writing, and arithmetic) to "courses", akin to major areas of
study. I was not attempting to determine or nominate students into a
grade level -- this would be a calculated measure based on credits
attained and almost certainly additional, more complicated rules.
[Indeed, I now see the underlying quantity is conspicuously absent from
my model (^: Like I said, it surely isn't perfect...]

In your case the "courses" table as given might very well serve as
"grade level" (i.e., Kindergarten, etc.) since each level probably has
specific required "classes", and perhaps you do not calculate credits to
determine a student's grade level.

"ClassYears" is there to conveniently aggregate classes over time. Same
with "Semesters". You may not need these, but then again they may be
useful to time-stamp classes.

I was trying to steer to the relationships between Students,
Assignments, and Grades, and realize the many-to-many relationship
between students and assignments. Looks like you have given this some
thought!

BTW in the diagram, an arrow pointing from A to B means "A is a member
of B", or "A belongs to B". Hence, "grades belong to students" and
"classes are members of courses".

Conversely, an arrow pointing towards B away from A reads "B has/have
A". Hence, "assignments have grades" and "classes have assignments".

Best Regards,
Click to expand...
 
Help! when I tried to put relationship in, I couldn't make any of them be
anything except 1 to 1.

Smartin said:
Serendipity said:
The PDF was a wonderful help! Could you please clarify a couple of things. We
are a Kindergarten through 8th grade school, so do we need Courses? In our
situation would Courses be Kindergarten and then Classes would be science,
reading, etc. Or would Kindergarten be another table up on the level of Class
Years and Semesters. I am assuming that Class Years is for example 2006-2007?
Thank you so much for your help.
Click to expand...


Glad to know you found that useful. I created it in response to someone
looking for a much more detailed model and I'm sure it's not perfect,
but it was a good exercise for me to conceptualize the relationships.

Part of the concept in the diagram was to relate "classes" (like
reading, writing, and arithmetic) to "courses", akin to major areas of
study. I was not attempting to determine or nominate students into a
grade level -- this would be a calculated measure based on credits
attained and almost certainly additional, more complicated rules.
[Indeed, I now see the underlying quantity is conspicuously absent from
my model (^: Like I said, it surely isn't perfect...]

In your case the "courses" table as given might very well serve as
"grade level" (i.e., Kindergarten, etc.) since each level probably has
specific required "classes", and perhaps you do not calculate credits to
determine a student's grade level.

"ClassYears" is there to conveniently aggregate classes over time. Same
with "Semesters". You may not need these, but then again they may be
useful to time-stamp classes.

I was trying to steer to the relationships between Students,
Assignments, and Grades, and realize the many-to-many relationship
between students and assignments. Looks like you have given this some
thought!

BTW in the diagram, an arrow pointing from A to B means "A is a member
of B", or "A belongs to B". Hence, "grades belong to students" and
"classes are members of courses".

Conversely, an arrow pointing towards B away from A reads "B has/have
A". Hence, "assignments have grades" and "classes have assignments".

Best Regards,
Click to expand...
 
Sorry, tried to post this here before, but I don't know where it went.

I figured out part of my not being able to get a one-to-many relationship. To
make sure that the properties of my foreign PK were the same as the PK, I
copies athe fields from the table where they were not a FPK and then renamed
them in the foreign table. In doing so, I also copied the indexed property of
"no duplicates." So you don't have to think on this one.


Serendipity said:
Help! when I tried to put relationship in, I couldn't make any of them be
anything except 1 to 1.

Smartin said:
Serendipity said:
The PDF was a wonderful help! Could you please clarify a couple of things. We
are a Kindergarten through 8th grade school, so do we need Courses? In our
situation would Courses be Kindergarten and then Classes would be science,
reading, etc. Or would Kindergarten be another table up on the level of Class
Years and Semesters. I am assuming that Class Years is for example 2006-2007?
Thank you so much for your help.
Click to expand...


Glad to know you found that useful. I created it in response to someone
looking for a much more detailed model and I'm sure it's not perfect,
but it was a good exercise for me to conceptualize the relationships.

Part of the concept in the diagram was to relate "classes" (like
reading, writing, and arithmetic) to "courses", akin to major areas of
study. I was not attempting to determine or nominate students into a
grade level -- this would be a calculated measure based on credits
attained and almost certainly additional, more complicated rules.
[Indeed, I now see the underlying quantity is conspicuously absent from
my model (^: Like I said, it surely isn't perfect...]

In your case the "courses" table as given might very well serve as
"grade level" (i.e., Kindergarten, etc.) since each level probably has
specific required "classes", and perhaps you do not calculate credits to
determine a student's grade level.

"ClassYears" is there to conveniently aggregate classes over time. Same
with "Semesters". You may not need these, but then again they may be
useful to time-stamp classes.

I was trying to steer to the relationships between Students,
Assignments, and Grades, and realize the many-to-many relationship
between students and assignments. Looks like you have given this some
thought!

BTW in the diagram, an arrow pointing from A to B means "A is a member
of B", or "A belongs to B". Hence, "grades belong to students" and
"classes are members of courses".

Conversely, an arrow pointing towards B away from A reads "B has/have
A". Hence, "assignments have grades" and "classes have assignments".

Best Regards,
Click to expand...
Click to expand...
 
Back
Top