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.
