Complex Query to determine status code

[psycoperl]

I am working on a project that will track what test(s) a student needs and assign them the status appropriate code.

I currently have 2 tables & 3 queries in my database
(tblStudent, tblATBCode, qryMaxMath, qryMaxReading, qryMaxWriting)

For Each Student in tblStudent (by tblStudent.StudentID) i need to determine the correct tblATBCode.ATBTestCode based on the students records where the Met value is either "Y" or "N"
tblATBCode.MetMA = qryMaxMath.MaMet
tblATBCode.MetRA = qryMaxReading.ReadingMet
tblATBCode.MetWO = qryMaxWriting.WoMet
(If there is no test record in a querys treat as if it was a "N")

I can't figure out where to start on this code. I am thinking that a query could do this but not sure. Any help you can provide will be great.

 

[SydneyGeek]

Part of the reason for the struggle could be the structure.
You need a third table to join tblStudent and tblATBCode, because the relationship is many to many (each student will sit many subjects, each subject covers many students).
This join table doesn't need many fields but it will, as a minimum, require
ATBStudentID (Primary key, Autonumber)
StudentID (Number, if you used AutoNumber for StudentID in the Student table)
ATB_ID (Ditto)

Join tblStudents to this table on StudentID, and join tblATBCodes on the ATB_ID.

Entering data into a setup like this is best done using subforms.
Student form will have a subform based on the join table, with StudentID as the master and child fields.
As you browse to each student you will see their ATB codes. Add a new ATB in the subform and it will be assigned to that student.

Denis

 

[psycoperl]

Thank you for the inspiration... I think I got the solution.