Question add and delete records in multiple tables

[matiyas]

I have 2 tables, Students information and Accounts information.
the two are related with the field "Admission number" which is common in both tables, which also acts as the primary key in the 2 tables,
i have Enforced ref integrity,and also enabled cascade deletes and updates.
the two tables also have 3 common fields i.e name,class,gender.

My Question is: when I delete records from Students table, it does fine and deletes related records in Accounts records, but when i add records in students table it does not reflect in Accounts table. Why?

What I want is: When i add records in the students table, the related fields in accounts table are also added. same to when i delete records.
how do i make this happen?

 

[pr2-eugin]

Hello matiyas, I think you have misunderstood the concept of Relationships.. There should be several examples on the W3 regarding this.. However I will just give you a start.. I will try to use similar scenario as you have.. Say we have two tables tblStudentInfo and tblAccInfo..

tblStudentInfo will have the students basic information.. StuAddmissionNo, StuName, StuJoinYear, StuAddress..
tblAccInfo will have the accounts for the student.. accId, StuAddmissionNo_FK, AmountPaid, PaidDate..

So as you can see the relationship between tblStudentInfo and tblAccInfo will be One to Many, which is One Student can have one or Many Account information.. This One side of the relationship is normally called the Parent record, and the many is called the Child record(s)..

If you have not enforced Referential integrity, and delete the Parent record, it will leave the child records with no parent also called "orphaned" records.. If you have enforced the rule, then deleting the parent will delete all associated child. This makes Referential integrity an important part of Relationships..

You have no need for the name age to be present in the second/child table.. They can be linked/summoned when needed.. When you add a new record, you can add more information, if nothing is written a blank record will be created.. Makes sense?

 

[matiyas]

mh!..i thought about it and decided to make a (one to one) relationship, enforced ref integrity and allowed cascade deletes/updates. This solved the updating problem in the tblStudents_information,
Since i wanted the updates to reflect to the tblAccounts_information on related fields, i simply made an update Query to the table to get the values from the tblStudents_Information.
Now i will just put an "on close" event to the form that i will create on tblStudents_information to run the update query.
All Solved

Mh! now on to the next one.
Thankss Eugin!!