listings (records) in query

[Guest]

I am a math teacher processing the grades for my school using Access 2000. The school is about 100 students - basically 6 teachers, 6 classes, and the students are transient i.e. present for about 3 months, but can be less or more. The students basically stay with the same group (class name such as AOT or Reg1) all day
I have (a) a table with the student's name and their class, (b) a linked table with their subject and letter grade (and class name at that time, and (c) form showing the subject (and teacher)
I have a query to show each student, their previous class and grade and their present class. The query uses the first 2 tables in a linked relationship with join properties #2, and the form isused in the criteria for each teacher. This is used to print a report for the teachers to write in the 2nd six weeks grade. Results
Student A - first six week's grade is listed and student is listed
Student B - no first six week's grade and student is listed
Student C - first 6 week's grade and no longer in school and is listed but 2nd 6 weeks area is blank
Student D - special schedule does not have Health class, not listed on Health teacher's grade list
Student E - special schedule, no Health or Art, not listed on Health or Art grade list
Student G - came in late of first 6 weeks, given history grade, now listed only on History teacher's grade list

My first concern is student G - since he continues to have Math, etc., but is not listed. How can this be corrected

My second concern is student E - since his schedule has been changed to include Health and Art

Looking to the future - the grade list will show both the first and second 6 week's grades and ask for the 3rd six weeks and semester grade. Are their any other problems I should expect

 

[Steve Schapel]

John,

What is the basis of the relationship between the two tables? Can you
post back with the SQL view of the query you are referring to?

 

[Guest]

SELECT Students.[KA#], Students.EntryDate, Students.[Discharge Date], Students.Alum, Students.LastName, Students.FirstName, Students.[M I Name], Students.ClassID, Classes.Class, Students.[D O B], Students.AlumDisch, [NCA Grades].GradeYr, [NCA Grades].GradePer, [NCA Grades].Grade6wk, [NCA Grades].Subject, [NCA Grades].grClass, [NCA Grades].LetGrad
FROM (Students INNER JOIN Classes ON Students.ClassID = Classes.ClassID) LEFT JOIN [NCA Grades] ON Students.StudID = [NCA Grades].StudI
WHERE (((Students.EntryDate) Is Not Null Or (Students.EntryDate)<=[forms]![jlw sdates]![3rd 6 wk s date]) AND ((Students.[Discharge Date]) Is Null Or (Students.[Discharge Date])>=[forms]![jlw sdates]![1st 6 wk s date]+14) AND (([NCA Grades].GradeYr)=[forms]![jlw sdates]![year] Or ([NCA Grades].GradeYr) Is Null) AND (([NCA Grades].GradePer)=[forms]![jlw sdates]![sem code] Or ([NCA Grades].GradePer) Is Null) AND (([NCA Grades].Grade6wk) Like "1st*" Or ([NCA Grades].Grade6wk) Is Null) AND (([NCA Grades].Subject)=[forms]![jlw teachers]![subject] Or ([NCA Grades].Subject) Is Null)
ORDER BY Students.LastName, Students.FirstName, Students.[M I Name]