sashasandy
I can tell from your select queries that your database is not properly normalized.
Until you properly normalize your database you are going to continue to bang your head into the wall at 100MPH(161KPH).
Once again, I ask you to go back and refer to the tutorials and other information I've linked you to in your other threads.
Just so you will get the idea of what a properly normalized
database could look like:
NOW KEEP IN MIND
I've done this at 03h00cst, based on a database I made some 20+ years ago - from memory (and my spelling typing maybe a tad off after 20hrs-uptime)
In the following:
PK = Primary Key, always autonumber
FK = Foriegn Key to the table that follows, numeric long 1:M
In the first pass of thought as to how I would setup my tables if I were to be tracking mulitple students thru multiple classes over multiple years:
tbl_students
[students_pk]
[...student only information...] = for these type entries, I mean, keep the information strickly to the topic of the table. In this case, [students_campusid], [students_fname], [students_lname], [students_title], etc...
tbl_instructors
[instructors_pk]
[...instructors only information...]
tbl_courses
[courses_pk]
[...courses only information...] don't be adding classroom numbers or building or any other information that might change from term to term that would be in a differnt table...
tbl_gradescale
[gradescale_pk] autonumber
[gradescale_pointscale]
[gradescale_letterscale]
[gradescale_lowerPercentRange]
[...gradescale only information...]
tbl_classes
[classes_pk]
[classes_fk_instructors]
[classes_fk_courses]
[classes_cycle] (year or semester... might be FK to YAT)
[...Other realated information only...] such as room number.
tbl_enrollment
[enrollment_pk]
[enrollment_fk_classes]
[enrollment_fk_students]
(so for each class, there would be a record entry for each student, 20 classes, 15 students, = 300 records and so forth. Sounds like a pain, however, the form can be made very simple for the end user to make multiple entries for a student for a given [classes_cycle])
tbl_assignments
[assignments_pk]
[...assignment only information...] such as title, weighting of total grade, etc...
tbl_assesments
[assesments_pk]
[assesments_fk_enrollment]
[assesments_fk_assignment]
[assesments_pointscale] numeric long
--- For [assesments_pointscale]: In the FORM I would setup an UNBOUND combobox with Row source would be to a query based on tbl_gradescale pulling the point scale, letter scale and lower range. THe "after update event" would record the correct point scale grade to [assesments_pointscale]. This is because you need to keep the point, as given, for the future just incase the ranges shift in tbl_gradescale... unless you want this change to reflect for all students for all times for all courses in which case a simple lookup at the query level will work and the CBO can be a bound control.
Now you see what I've done here?
Let's say you want to find every grade a student has for last term...
Q_A) Find the PK for the student in tbl_students
Q_B) Find all the classes in tbl_classes for the given term
Q_C) From tbl_enrollment return only those record(s) that match records in Q_A and Q_B
Q_D) From tbl_assesments return only those records that also match against Q_C
Now you could have a running average of [tbl_assesments]![Assesments_pointscale] based on the records returned from Q_D... there are plenty of examples on how to do this on the net. You can add the weighting in, etc... once you have this... it should be a simple matter to convert [tbl_assesments]![Assesments_pointscale] to a letter grade by DLOOKUP() against "tbl_gradescale"
You should be able to figure out how to get a course, class, or instructor specific infromation from here.
Rabbit, NeoPa, or one of the profesional programmers that visit may be able to tweak what I've suggested to make things better.