Cascade update/delete ??

[bob_ed]

Hi - I'm a secondary school teacher (and novice Access 97 user) trying to set up a recording system for other teachers at school. In order to cut down on the number of fields in one table I have set up a master table with details of each pupil, then subject tables (up to 30 fields in each) for English, Maths, Science etc. Every relationship is one-to-one back to the master table. The primary key in every table is a pupil ID number. Referential Integrity, Cascade Update, and Cascade Delete have all been checked.

Please can someone tell me how I add a new pupil or delete an existing pupil and have that action replicated in all the subject tables? I've tried using a query and also just a form but all I end up with is an alteration to the master table.

 

[Mile-O]

What's your actual structure...what you have written sounds wrong.

You, essentially, need a table for pupils, a table for subjects, a table for teachers thus far.

What sort of information are you storing in these numerous subject tables? Are they all identical but for the name or do they all have their own peculiarities?

 

[bob_ed]

Thanks for the reply Mile-O-Phile. The db has evolved from one table which contains details of all pupils (usual stuff: name, year, dob, photo etc.) and sets of marks and grades for one subject - IT. This is currently holding data on about 800 pupils and has successfully been in use for about 3 years with various queries and forms to display data and pupil photos etc.

Other subject teachers are showing an interest and wish it to be modified for their subject marks, results and grades while using the existing data on pupils names etc. from the master table.

Teachers names are generally not needed but some subjects might use teacher initials only as group/class identifiers (one field in the subject table I thought.

Basically I have set up a couple of subject tables (different field requirements for each) with a primary (foreign?) key in each table which is the pupil ID number linked to the same pupil ID number in the master table in a one-to-one relationship.

New queries are working fine - e.g. a list of all pupils in Year 8 with their photos (from the master table) and marks for term 1 in English (from the English table).

The only problem I have is as stated in the original post - entering new pupils or deleting existing pupils when cascade just does not seem to work.

 

[MattS]

If I understand correctly:

Referential Integrity: You can't add a pupil to your subject tables if they do not exist in your master table

Cascade Update: Change the data in any of the fields in the master table that are part of the relationship with the subject table (i.e. pupil id), and the changes will automatically be made in the subject table

Cascade Delete: Delete a pupil from the master table and they will automatically be deleted from any subject tables

If you want to automatically add a pupil to the subject tables at the same time you add them to the master table, you should try update queries.

HTH,

Matt.

 

[bob_ed]

Thanks Matt but its OK now. I really don't know why, but I originally I created a form with fields bound to all the relevant tables, to add or edit pupils details. Every time I tried to change something I kept getting an error message saying something like "cannot change this data set".

That's when I posted the question. Eventually I just created another form in the same way and it works perfectly!! It will add new pupils, delete old pupils and modify pupil data. This is replicated in all linked tables.

I really don't know why the first form failed - the second one seemed identical - I even compared all the properties and could not see any difference. There you go!

Thanks
Bob.