Adding multiple repcords in related table

[swilliams2785]

I have a table with student’s demographic information. I have another table with student’s grades. The two tables share the student’s ID Number field. The grade table has four records for each student, one for each grade period. In the application I will need to add and delete students and in turn add or delete the four corresponding records in the grade table. Is this done via relationships or will I need to write VBA code? If I need to write code what event should it be tied to?

 

[YNWA]

If you want to link the two IDs together so all grade info relates to the student, then go into relationships and link ID in demo table to studentID in grade table.

I would advise you to add a field called ID to demo table and use Autonumber and make Primary key, then add field to grade table called studentID and use Number type. In the grade table you would also have another field called ID with autonumber and PK.

When you link the two together you then use ref int and cascade update/delete.

The idea is 1 student has many grades. So you would have 1-many relationship from ID in demo table to studentID in grade table.

Then as a completely different field you would have the students ID number if I am reading it correctly in thinking that this field is their student reg number or something similar?

 

[YNWA]

Also by add and delete do you mean delete students and grade from the database?

If so you can add buttons to your form using the wizard that will allow you to do this.

Not sure if this is 100% but I think in the relationships when you select referential integrity and you select cascade update, also select cascade delete and this will allow you to delete student and all grade info when deleting the student.

 

[swilliams2785]

Attached is a sample database with demographic and grade tables as well as a form for managing the contents of the demographic table. I have the relationships set up and the delete function works as expected, however, when I add a new student I need tblMarksEntry1 to contain 4 new records with the STDNO value equal to the STDNO value in the demographic table, the values 1,2,3, and 4 in the GP (grade period) fields and a text string of 11 zeros as the contents of the NSx field in each new record.

 

[SOS]

99% of the time you don't need to use Cascade Updates. That only changes the foreign key if the primary key changes and if you set up your database properly so that you are using a numeric primary key (usually autonumber) you will never need to change your primary key.

Cascade updates, on the other hand, can be useful so as to avoid orphan records as it will delete those records for the many side if the parent record is deleted.

You need to be careful on where you put Cascade Deletes though as not all deletions of a parent would want the children to be deleted. This could be in situations where you are using a lookup table connected by a key to the main table.

 

[SOS]

Just looked at your database and your tblMarksEntry1 is definitely not normalized. You should not have fields like DA/DAX, AG, AGX, etc. You should be entering ROWS not colums for the course and mark.

I think we need to steer you back to a normalized table design.

For example:

tblCourses
CourseID - (PK) Autonumber
CourseDescription - Text

tblStudentCourses
StudentCoursesID - (PK) Autonumber
StudentID - (FK) Long Integer
CourseID - (FK) Long Integer
CourseYear - Integer
CoursePeriod - Integer
CourseMark - Integer


And then you would be able to add as many courses as necessary (never having to revise everything because you added a course) and you would not have a problem trying to get data back out in a meaningful way. How bad do you think it would be in your current setup to get the grade average? Wicked, if not impossible, due to so many fields.

In the setup I showed, you would be able to get the mark averages easily, as well as other data.

 

[swilliams2785]

Thanks for the suggestion, I'll look into it when I have time. I was able to write code that solved my original problem.