student database design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a student database used to track students in our major. the students
are required to be advised each semester and this is noted. i currently have
a field for each semester (Fall05, Spr06, Fall06, etc.), but this is getting
long. besides making this data more efficient, i'd also like the database to
automatically put "n/a" in the advising field if the student graduated or
changed majors.

any ideas or templates?

-dm (access nOOb)
 
This will seem like a lot for doing simple tracking, but once it is set up it
will be easy to maintain.

tbl_Students
StudentID (PK)
StudentInfo
Graduated (yes/no)
ChangedMajors (yes/no)

tbl_Semesters
SemesterID (PK)
SemesterText

tbl_Advisements
AdvisementID (PK)
SemesterID
StudentID
Advised (yes/no)

This should allow you to sort out anyone that has graduated or changed
majors, and will allow you to keep adding semesters. You can then use a
form/subform to look at any particular student to see if they have been
advised, and for which semesters (you could use a query to limit to the
current semester).
 
that helps a lot...but i still need a little more direction.
can you provide more info on the "form/subform to look at any particular
student to see if they have been advised, and for which semesters"

i think it's on the verge of clicking with me...i just need a little nudge.

thanks for the help.
dm
 
Make a query based on all of the fields from tbl_Advisements, add all fields
from tbl_Students except for StudentID, and all fields from tbl_Semester
except for SemesterID (the excluded fields are already included in the
original table, so you don't need to duplicate them for the query).

Once the query is made, use a wizard to create a form based on that wizard,
selecting all of the fields. The wizard will ask you "How do you want to
view your data?" and you will choose "by Students" and Form with subform(s).
Finish the wizard. You will now have a form and subform, where you can
choose the StudentName on the form, and the Semesters for that student will
appear in the subform. You will probably want to clean up the form, changing
labels and hiding the things you don't need to see. Of course, the semesters
and students won't appear until you actually add them, and you might want to
make them as a drop-down in the form. But this gives you a good general idea
of how to present information using your data.

The biggest lesson to learn from this, is that properly normalized tables
will allow you to present data in many possible ways, sometimes using the
same query, but using a slightly different approach. If you ran the Form
Wizard again, using the same query, but instead choose to view by semester,
rather than by student, then it would give you a list of all students (and
their advisement status) in the subform, by semester in the main form. You
could also make a copy of the query, calling it by a different name, and then
put some filters in it. You could filter by a certain semester, with
students that have not graduated or changed major, and by advisement not
having been done. Then you would have a list of students that require
advisement for that semester.

The caveat is that you do need to keep your database up-to-date, so that the
information that you pull from the data will be accurate.
 
That worked great! Thanks!

mnature said:
Make a query based on all of the fields from tbl_Advisements, add all fields
from tbl_Students except for StudentID, and all fields from tbl_Semester
except for SemesterID (the excluded fields are already included in the
original table, so you don't need to duplicate them for the query).

Once the query is made, use a wizard to create a form based on that wizard,
selecting all of the fields. The wizard will ask you "How do you want to
view your data?" and you will choose "by Students" and Form with subform(s).
Finish the wizard. You will now have a form and subform, where you can
choose the StudentName on the form, and the Semesters for that student will
appear in the subform. You will probably want to clean up the form, changing
labels and hiding the things you don't need to see. Of course, the semesters
and students won't appear until you actually add them, and you might want to
make them as a drop-down in the form. But this gives you a good general idea
of how to present information using your data.

The biggest lesson to learn from this, is that properly normalized tables
will allow you to present data in many possible ways, sometimes using the
same query, but using a slightly different approach. If you ran the Form
Wizard again, using the same query, but instead choose to view by semester,
rather than by student, then it would give you a list of all students (and
their advisement status) in the subform, by semester in the main form. You
could also make a copy of the query, calling it by a different name, and then
put some filters in it. You could filter by a certain semester, with
students that have not graduated or changed major, and by advisement not
having been done. Then you would have a list of students that require
advisement for that semester.

The caveat is that you do need to keep your database up-to-date, so that the
information that you pull from the data will be accurate.
Click to expand...
 
Back
Top