How do I do this

  • Thread starter Thread starter Eddie Jackson
  • Start date Start date
E

Eddie Jackson

I have a form "Students" name addresses etc on it I also have a combo box
which looks up values in table "Courses".

So now I have access to a student and the course they are on. However when
the course finishes some students will enrol on a new course (not all) I
need a sub form on "Students" to tell me what previous courses a student has
been on if any, the sub form would be based on a table "Course History" How
do I tie all this together?
Regards
Eddie
 
Hopefully, your database structure is correct. If so, it will be pretty
straigt forward. You should have these 3 tables:
Student - A List of Students
Course - A List of Courses
Enrollment - A List of which students are/were enrolled in which courses.
The relationships should be:
Student -> 1 to many -> Enrollment <- 1 to many <- Course

So, if you have designed your database correctly, then all you need for your
subform is a query based on Enrollment, filtered by Student.
 
You have provided few details, but it sounds as if you are storing course
information in the Students table. In a typical student/courses database
you would have a Students table (tblStudents) and a Courses table
(tblCourses). Since each student can take many (i.e. more than one) courses
and each course can be attended by many students, there is a one-to-many
relationship between Studetns and Courses. In order to resolve this a third
table is needed (tblEnrollment).

tblStudents
StudentID (primary key, or PK)
FirstName
LastName
(and other personal information)

tblCourses
CourseID (PK)
Department
Title
Date
etc.

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
CourseID (FK)
Grade
(and other information specific to a particular student in a particular
course)

Primary keys are assigned in table design view. The foreign keys need to be
the same data type as their namesake primary keys. If the data type for the
PK is autonumber, the corresponding FK needs to be number; otherwise they
both need to be the same. Primary keys and data types are established in
table design view.

Create a relationship between the PK and FK fields: In the Relationships
window, drag StudentID from one table on top of StudentID in the other
table. Click Enforce Referential Integrity. Repeat for the other table.

Create a form (frmStudents) based on tblStudents and another
(fsubEnrollment) based on tblEnrollment. On fsubEnrollment, create a combo
box (you can use the wizard) bound to CourseID. In form design view, drag
the icon for fsubEnrollment onto frmStudents. This will let you populate a
student's record with course information. Once this works you can work on
restricting the list of courses to current courses, or viewing all courses
the student has ever taken, or anywhere in between.

You can build a form or report based on tblCourses, with a subreport based
on tblEnrollment, to view the enrollment in a particular course. You can
also reverse tblStudents and tblCourses in the above to create a main form
based on tblCourses, which you can then populate with student names. In
either case you can use a form or report to view the information based on a
student's course load or a course's enrollment roster.
 
Back
Top