Create a new record with information from two other tables
Thanks ahead of time for help with my newbie question!!
I am a music teacher creating a database to manage performance information for my nearly 500 students. My database currently has three tables.
Table 1 - Student information: This table has all the regular information you'd expect: name, grade, teacher, class, birthdate, student ID, etc..
Table 2 - Music Standards: Each record contains: Category (performance / literacy / critical), Skill Ladder (ladders of related skills within each category, such as "Literacy - Rhythm", and "Performance - Sing"), Standard Name, Description, Grade Level, and Standard ID Number.
Table 3 - Music Notes (related to Table 1 with Student ID and Table 2 with Standard ID). This table will bring together notes about the student's performance toward a specific skill, such as Student ID, Standard ID, Notes, Mastery (yes/no), Date, score, and container.
To begin entering a new note, I'm currently starting on the student side with a "New Note" button. This runs a script that opens a data entry layout based on Table 3. It sets the student ID value to the currently selected student, brings over the first and last name, and sets the current date (which can be changed if I'm behind). Now, I need to "lookup" the standard ID from table 2 for which I'd like to take notes. I imagine the easiest way (for the user) would be to check one of three radio buttons to select the category, then use a dropdown menu populated with the skill ladders from the chosen category, then use another dropdown populated with the standards from that skill ladder. Finally, the matching standard ID would be set in the form, and notes can be entered.
How do I do this?
Thank You Again!!!
Note: I could (and may) go the other direction, starting from the skill, but I would face a very similar situation getting the student ID from Table 1 (Grade, Class, Student, ID).