I have a database for students. I have to organize a huge amount of info for each one, like address, gpa, test scores, etc etc. I want to put these catagories into separate tables rather than have one huge table. I want to be able to add a new student to the main table, and have the related tables automatically add a new (blank) record for that student - as if it were all part of the same table. Is that possible? Thanks!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Auto update new records for related tables?
- Thread starter Alexx
- Start date
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
How you do this depends on your table structure. A minimal structure for what you are after is
(1) Students, with Name, Address, Phone, StudentID, etc.
(2) Courses, with CourseID, CourseDescription, StartDate, EndDate, TeacherID, etc. (TeacherID is a foreign key from the Teachers table, which holds all details on your academic staff)
(3) Tests, with TestID, StudentID, CourseID, TestDate, Score, etc.
GPA is a calculation and should not be stored in a table. You'll want to set up a query to calculate that for you as the student's test results are updated.
Have a look at the Northwind database to see some ideas for setting up the forms. You could have frmStudent, with subCourses and subTests as subforms, set so that selecting a student shows all courses enrolled or attempted. Clicking any course shows all test results for that course.
If you get stuck, send another message.
Denis
(1) Students, with Name, Address, Phone, StudentID, etc.
(2) Courses, with CourseID, CourseDescription, StartDate, EndDate, TeacherID, etc. (TeacherID is a foreign key from the Teachers table, which holds all details on your academic staff)
(3) Tests, with TestID, StudentID, CourseID, TestDate, Score, etc.
GPA is a calculation and should not be stored in a table. You'll want to set up a query to calculate that for you as the student's test results are updated.
Have a look at the Northwind database to see some ideas for setting up the forms. You could have frmStudent, with subCourses and subTests as subforms, set so that selecting a student shows all courses enrolled or attempted. Clicking any course shows all test results for that course.
If you get stuck, send another message.
Denis
Upvote
0
Similar threads
