Mi***********@gmail.com wrote:
I'm bulding a database of students and their yearly performances. I
have a student table with all of their permanent information, and a
academic performance table that has different fields representing their
academic performance for the year. Within the academic performance
table, I have a field for Fall Semester GPA, and Spring Semester GPA.
I'd like to create a query that outputs all academic information for
students in a particular semester. To do that, I'd like to create a
dynamic SQL Query that takes in an indicator from the user to include
either the fall semester GPA, or the Spring Semester GPA among the
output, but not both. Is this possible?
Hello,
Your first hurdle is to understand how to correctly model this data in a
relational database.
In your example you have two entities: students, and semesters. Students
are enrolled in many semesters, and semesters have many students enrolled.
This is a classic "many-to-many" relationship. At its simplest this is
best represented in three tables.
Your students table, which is probably OK, should have information about
the students as people. Nothing about their classes, grades or semesters
belongs here.
Here is where you need to rethink your design:
Rather than have one table with one student and many semesters across
the horizontal (a spreadsheet design) you should break this up.
A table of semesters, enumerating semester information only.
A "joining table" that pulls together one student and semester per row.
It is now much easier to query the joining table for a particular
student and semester.
I have attempted to construct a data model of students, classes,
courses, etc. It is somewhat wider than your scenario but if you would
like to see how these many-to-many relationships play off each other,
feel free to view this:
http://vfdrake.home.comcast.net/files/students.pdf
--
Smartin
