Nested Loops Question
I am a music teacher with 500 students, and I'm creating a database to track 262 music standards. It's coming along well, and I need help with a nested loop situation. If something about the structure needs to change to make this process easier, I'm open to suggestions.
I've attached a picture of the tables and relationships.
Each student has a profile in Student Data. Each music standard has a profile in Music Standards. Each note in Music Notes is related to both a student and a standard.
I wish to print a report for each student every 9 weeks that lists each standard and either the date of mastery, or the highest score that has been achieved so far (if not yet mastered).
I created the Music Achievement Report table to hold one record per student per standard plus the mastery_date or max_score info. What I need to do now is populate these fields, and I think it's going to require a nested loop, something like this:
Loop 1: Get the list of student IDs from Student Data - Start with the first student ID
Loop 2: Get the list of standards from Music Standards.-Start with the first standard
(thiscould just be math instead of a search)
Loop 3: Find all records in Music Notes with the current student and standard.
If mastery = "Yes", write the date to the related mastery_date field in Music Achievement Report.
If none of the mastery fields were "Yes," write max (score) from the set into max_score in Music Achievement Report
I'm not sure if this is doable, and if it's not, what to do instead. Thanks ahead of time for thinking about this and any responses. :-)
Patrick R. Lollis, Music Teacher, Grapevine, TX