Help with query - problem getting the right records

[BeeMarie]

Hi y'all,

I've been struggling with this query for over 2 weeks and have tried so many ways to do it I've lost count.

I have a student DB. Current student's records can be queried by the SchoolYear field with a function fGetFiscalValues which will return only students in the current year. This is good because I don't want to include any students in my query who are not current students.

However, I want to get current students, and then compare their Math and Reading Grade against their Math and Reading grade for previous years. Since the method I'm using to get current students doesn't include previous years, I can't figure out a way to do this.

I think I need an outer join, but when I do it that way, the query records don't show the StudentID for the outer joined records, so then I don't know what to do with them.

I have the same problem with comparing the grades to a current assessment. I only want to look at the assessment they took the current year, but I want to compare the results to their grades for this year and any previous years they also attended.

Can anyone give me some advice? I've run out of ideas again.

Thanks,
~BeeMarie

[NeoPa]

I think we can probably help here, but we would need MetaData for the table(s) used and the code for the function too.
Here is an example of how to post table MetaData - please use this same format :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[BeeMarie]

Table=tblStudents

Field; Type; IndexInfo; Comment
StudentID; Long Int; PK; Student’s ID
StudentLastName; String
StudentFirstName; String

tblStudentEnrollment
StudentEnrollmentID > PK A# long int
StudentID > fk to tblStudents, long int
fkGradeLevelID > fk to tluGradeLevel (long int, fk string)
fkSchoolsID > fk to tluSchools (long int, fk string)
SchoolYear > qtluSchoolYears (string like 04-05, 06-07, etc.)

tblStudentClassGrades
StudentClassGradesID > PK A#
fkStudentEnrollmentID > fk to tblStudentEnrollment, long int
fkSubjectID > fk to tluSubjects (long int, fk string)
fkClassGradeID > fk to tluClassGrades, (long int, fk string)

The function is fGetFiscalValues()
It returns the current school year as a string like 06-07 which allows me to query students who are current.

I really don't want to add another field like "exit" or "status" unless I absolutely have to, because I am concerned that these fields wouldn't be kept up to date. The purpose of this db is only to analyze grades and scores, it is not intended to track anything that isn't needed for that purpose. I was just thinking that maybe I could make the query put a date based on the SchoolYear string. Then I could query on a date range of 3 years. I'm not sure how I'd do that though.

I appreciate any advice. I am sorry if I put more info than is needed to help.

~BeeMarie

[NeoPa]

BeeMarie,

I don't have any trouble with extra data, but as this is in quite a different format from what I was expecting (as laid out in my earlier post) it may take me some time deciphering what is meant by what.
I need to pack it in for the night now anyway, but I'll try to get back to this in the coming days (Normally I'd say tomorrow but I know my schedule is hectic until Thursday so I don't want to make any promises I can't keep).

[BeeMarie]

tlu = table lookup...not that important to this query really.

I figured out that putting a date range in place of the school year still doesn't tell me if a student is current. I don't want to waste anyone's time...I am thinking the only way this is going to work is to add some kind of Status field to the Students table.

Thanks,
~BeeMarie

[NeoPa]

Using your data structure, how would YOU determine if a student were current or not?
I would guess it's something to do with enrollment and the current year?