Program and Student Enrollment

[UnknownJoe]

I am looking at designing a more simplified database that does 3 things:
(1) Allows for input of College Courses
(2) Allows for input of Student Information
(3) Allows for Enrollment of various students into various courses at
different times and locations

- I have done this before but not very efficientally (1 table with many
records). Ideally, for this new DB, I was wondering if this was the besy way:
Program Table - ProgramID, Name
Student Table - StudentID, FName, LName, DOB, Phone, etc
Location Table - LocationID, City Name
Transaction Table (???) - TransID, ProgramID, Program Name, StudentID,
Student FName, Student LName, Start Date, End Date, LocationID

- I hope this makes sense. And if so, here is my other question: If I use
the Transaction table to select a ProgramID, can I have the Program Name
field automatically populate in the table instead of having to select it
myself - the same goes with automatically populating the Student FName and
LName once I choose a Student ID.

Thanks in advance.

 

[Beetle]

You're on the right track, but there is no reason to redundantly store
the program name and student name in your transactions table. You
would use, for example, a combo box in a form to display the correct
name based on the key value that is stored in the table. Your table
structure might look like;

tblPrograms
*********
ProgramID (Primary Key)
ProgramName

tblStudents
********
StudentID (PK)
FirstName
LastName
DOB
Phone

tblLocations
*********
LocationID (PK)
LocationName

tblTransactions
***********
TransactionID (optional PK. You could also use a composite PK)
ProgramID (Foreign Key to tblPrograms)
StudentID (FK to tblStudents)
LocationID (FK to tblLocations)
StartDate
EndDate

Then you would create, for example, a main form based on the Programs
table with a subform based on the Transactions table. In the subform
you would use combo boxes for selecting the ProgramID, StudentID and
LocationID. These would *store* the ID values but *display* the names.

You could have additional similar forms for Students/Transactions and
Locations/Transactions

 

[UnknownJoe]

Thanks for the quick reply.

I attempted your suggestion and just have a couple of questions.

When I create the Program Form (lets say the course is Biochemistry), it
lists the Program Name and Program ID at the top. Then I create the subform
based on the Transaction Table with combo boxes for the ProgramID, StudentID
and LocationID. I choose the ProgramID again, then choose a StudentID and
LocationID.
I checked the transaction table afterwards and it lists a record for each
Program and Student that I entered into the Transaction Subform. Wouldn't
that be the same thing as entering duplicate data into the Transaction table
without using a Subform?
Basically, I would like to be able to enter a Program, choose a location
with Start and End Dates, and assign multiple students to the particular
course. Many of the programs have the same name, but are taught at different
locations at different times with many different students.

Sorry for the confusion. I apologize in advance.

 

[Beetle]

OK, I misunderstood what you wanted originally (and looking back at
my original post, there was a flaw in how I told set up the form -
not taking enough time with my reply, sorry).

Anyway, here is a revised suggestion based on what I think you want.

tblPrograms
*********
ProgramID (Primary Key)
ProgramName

tblStudents
********
StudentID (PK)
FirstName
LastName
DOB
Phone

tblLocations
*********
LocationID (PK)
LocationName

tblCourses
***********
CourseID (PK)
ProgramID (Foreign Key to tblPrograms)
LocationID (FK to tblLocations)
StartDate
EndDate

tblCourseStudents
*************
CourseID (FK to tblCourses)
StudentID (FK to tblStudents)
Any other attributes that are specific to *this* student in *this* course

In the above table I would just use CourseID and StudentID as a combined PK.

Then your main form would be based on tblCourses (with combo boxes for
ProgramID and LocationID), with a subfrom based on tblCourseStudents.
CourseID would be the Master/Child link between the main form and
subform, with a combo box for selecting the Students.