Database design for student elective scheduling

[Guest]

I am new to this group and a relative newcomer to Access. I've been using a
rather cumbersome Access database for the past 2 years to schedule our 580+
students to electives. There has to be an easier way. So I have spent the
last 2 days designing a new database. I am so close, yet so far, from having
it ready for prime time. I'm hoping you experts can walk me through what I'm
doing wrong.

I have a table of students, a table of teachers, a table of electives.
I have built a query for each timeslot that shows me which courses are
available during each period.
What I want to do now is to design a form that will allow me to type in a
student ID (primary key) and it will automatically fill in the student's last
and first name. Then I can use the above mentioned queries to pull down and
place students into an available class.

Is this doable? Can somebody help me figure out how to make the form
autofill?

Thanks in advance!

MA

 

[Allen Browne]

You have:
Student table, with StudentID primary key;
Teacher table, with TeacherID primary key;
Elective table, with ElectiveID priarmy key.

Now, one elective is offered many times over the years, so you need:
Class table, with fields such as:
ClassID AutoNumber
ElectiveID which elective is offered
StartDate when this instance of the elective starts
CoordinatorID relates to Teacher.TeacherID

Students then enroll in a class, so your Enrol table will have fields:
EnrolID AutoNumber
StudentID who enrolled.
ClassID which elective instance they enrolled in
EnrolDate when the student enrolled in this class
PassDate date the student was credited with completing this
elective successfully (blank until then.)

The interface will be a main form bound to the Student table with a subform
bound to the Enrol table. The subform will be in continuous view (showing
one class per row), and will have a combo box for selecting the Class the
student is enrolling in.

Depending what else you need to track you may need other tables also,e.g.:
Attendance (one record each time a student attends a class):
AttendID primary key
EnrolID what class
StudentID which student
AttendDate date when the student attended.

Assignments table (one record for each assessment)
AssignID primary key
ClassID which instance of the elective required this
assessment.
DueDate when this assessment is due
Marks possible marks for this assessment

Submit table (one record each time a student submits an assessment.)
AssignID which assignment
StudentID who submitted it
SubmitDate when they submitted it
Score the actual grade recevied.

 

[Guest]

Thanks so much, Allen. I'm going to work on perfecting this bear over the
next few weeks. I have something set up now that is 100% better than what I
WAS using. Thanks for being my expert in residence.

MA

 

[Guest]

Here's what I have:

Students Table:
StudentID (primary)
LastName
FirstName
TchrID (for homeroom teacher's name)
Grade

Teachers Table:
TchrID (autonumber primary)
LastName
FirstName

Electives Table:
ElectiveID (autonumber primary)
Name (course title)
TchrID (who's teaching it)
Room (location)
SectionID (denotes 1st sem, 1st qtr, 3rd pd. as 113, etc.)

Then I created queries for each SectionID to list each course offered during
that time slot:
113 lists the 15 courses offered during that section.

Then I created tables for StudentSchedules. Grades 2 and 3 students take
electives during 3rd and 4th period only. So that table has is named
StudentSchedule2-3 and has:
StudentID: (number)
ElecA1: a pull-down list from the query for what is offered sect. 113
ElecA2: a pull-down list from the query for what is offered sect. 114
ElecB1: a pull-down list from the query for what is offered sect. 123
ElecB2: a pull-down list from the query for what is offered sect. 124

This much works beautifully.

Now, where I'm running into problems is with getting it to run queries to
list the students in each class. I want to be able to run 3 kinds of reports:

1. Homeroom report for teachers so they will see where each of their
students is going to different electives for the quarter.

2. An elective roster for elective teachers to know who is assigned to
their classes.

3. A student schedule report that lists the students name, homeroom class,
grade, and the 2 classes he is assigned to for the quarter (or 4 for the
semester).

I can't seem to get the queries to go fetch the information from all the
different tables and pull it together into one report. I've probably left
out a relationship somewhere along the line, but where, I'm not sure.

And one additional "issue"... I've just created this database from scratch,
but every time I open it, I get a warning that says it contains code that was
intended to harm my computer. I figure this is from some kind of macro, but
can't figure out how it got in there since I'm the one who created the
database.

Am I in over my head or am I as close as I feel like I am?

Thanks in advance!
MA

 

[Allen Browne]

The warning message you refer to at the end of your post occurs in Access
2003. Microsoft got a bit carried away, and decided to warn you not to use
your own database every time you open it. To avoid that silliness, go to:
Tools | Macro | Security
and set it to Low. That setting make A2003 behave the same as previous
versions.

Now for your specific questions.

#1. Homeroom report
I'm not convinced these tables are correct.
One elective will be offered multiple times (over different semesters, or
possibly simultaneous streams.) In the previous reply I suggested a Class
table (for each instance of the elective). Perhaps you don't like that name
but you do need 2 separate tables for the Elective (the course that is
offered periodically) and the ElectiveInstance (the particular one offered
in period 3 or Semester 2 of 2006 in room 4 by teacher 99.)

You will then have another table for the students enrolled in that
ElectiveInstance (which fufils the same function as your StudentSchedule2-3
table seems to.) This ElectiveInstance table should have different fields
for:
TheYear
TheSemester
ThePeriod
ElectiveID which elective this is
and so on.

Once you have that, you can create a query based on tables Student,
Elective, ElectiveInstance, and Teacher, and get at the fields for the year,
semester, period, room, and teacher for each student.

#2 Yes, that solves this one also.

#3. This one too.

 

[Guest]

I GOT IT!!! I FINALLY GOT IT!!!!

Here's what it ended up needing:

Students:
StudentID (#)
LN
FN
TchrID
Grade

Teachers:
TchrID (a#)
LN
FN

Homerooms
HRID
TchrID

Sections
SectionID
Year
Semester
Quarter
Period

Classes
ClassID
CourseTitle
TchrID
SectionID
RoomLocation

Enrollment2-3 (4-5, K-1) (Three separate tables)
EnrollmentID
StudentID
Elec13 (15, 17)
Elec14 (16, 18)
Elec23 (25, 27)
Elec24 (26, 28)

Then my Homeroom Query pulls from:
Homerooms, Teachers, Students, and Enrollment2-3 (or whichever).

I think this is going to work! Thanks for making me stick it out and figure
it out! Your help has been most appreciated!!!

MA

 

[Allen Browne]

Good news. Well done.