I evidently don't make clear what I am doing.
I do have the tables you recommended.
Students
Courses
Enrollment(Current Classes)
In addition, I have
Course History
Course Requests
Plan
4YearStudentPlan (This is where we record what courses the student will need
to complete the required number of credits for graduation. For Example,
College Prep requires 8 LA classes, 8 Math Classes, 6 Soc St., etc.)
Many tracks can have the same courses (from the Courses table) as part of
the 4 year plan. Theoretically, every student essentially could have a
different plan. I have the "General Plans" in the Plan Table, from which we
populate a StudentPlan initially. Once the StudnentPlan is initially
populated, the counselor goes into the student's plan and customizes it based
on Elective classes, or Failed classes, etc.
Thus, after the initial meting, the counselor only looks at the StudentPlan
for each student to determine progress toward graduation.
The Enrollment may, or may not, echo the 4 year plan. For example, if a
student fails a class, then the courses he takes may not happen in the order
of the plan, however, the plan stands as those are the courses needed for
graduation.
I have the courses in the student's plan grey out once the student passes
the course. That way, anyone looking at a student can see at a glance, what
courses are needed toward graduation, and which have been satisfied. There
are Tabs for the Current Classes, Course History, and Course Requests
I have built it and have it working now the way I need now. I ended up using
Macros to perform some of the functions (because I do not know much about
Visual Basic).
I'm certain that there are other ways to build this, but I do need the
Tables that I have listed for everything to work.
I really appreciate your feedback, and I have found many answers through
this news group. If I could find a comprehensive class in Access and Visual
Basic, I would jump at the chance to take it. The ones I have taken have been
basically a waste of time and taught by people with limited knowledge.
The most knowledgable folks I've found have been in this newsgroup. Again,
thank you and I really appreciate your help.
John Vinson said:
I have one field for each core Academic area and the semester in which the
student is slated to take the course.
For Example: FrLAS1, FrLAS2, FrMAS1, FrMaS2 etc. (8 for each year, 32 in all)
ie:{FrLaS1=Fr(Freshman)LA(Language Arts)S1 (Semester1)}
This is called "Committing Spreadsheet" and it's a very common error
in table design.
"Fields are expensive, records are cheap." You should consider
properly normalizing your design using THREE tables in place of this
one:
Students
StudentID
LastName
FirstName
<other biographical data>
Courses
CourseNumber
Class <e.g. "Fr", "So", "Jr">
Area <e.g. "LA", "MA", ...>
Level <honors track, technical track, etc.>
CourseName
<other info about the course itself>
Enrollment
StudentID <who's assigned to the class>
CouurseNumber <which class is she assigned to>
What is entered into each of these fields is the course for which the
student is slated based on his academic track. Therefore, for Language Arts,
an Honors student takes a different course than a Technical Student.
I've been playing around with this and an Update Query accomplishes this -
as the student's individual plan need to be initially populated based on his
academic trackand an update query does this based on the chosen track.
I now need to figure out how to limit this to just one student at a time.
(I've been away from Access for a few months and it is amazing what I have
forgotten)
Let's get the table design correct first. You can use a Query which
references a Form for its criteria, using
=[Forms]![NameOfForm]![NameOfControl]
as a criterion to select just one studentID for example.
Once I run this query, the users will work with the student on another form,
where they can customize his plan assigning Electives etc.
Is this the wrong way to develop this??
Yes, I'm afraid so. :-{(
John W. Vinson[MVP]
