If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Program and Student Enrollment
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. |
#2
|
|||
|
|||
Program and Student Enrollment
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 -- _________ Sean Bailey "UnknownJoe" wrote: 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. |
#3
|
|||
|
|||
Program and Student Enrollment
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" wrote: 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 -- _________ Sean Bailey "UnknownJoe" wrote: 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. |
#4
|
|||
|
|||
Program and Student Enrollment
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. -- _________ Sean Bailey "UnknownJoe" wrote: 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" wrote: 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 -- _________ Sean Bailey "UnknownJoe" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|