Updating Related Tables

[123James]

Hi everyone!

My names james, im 18 and im a data manager for a school.

My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.

The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?

Thanks.

 

[KeithG]

How are you entering the grades? With a Form?

 

[123James]

All data entry is through user forms as it is done by teachers, not myself.

 

[KeithG]

Do you have a main form and a sub-form? If not I would create a subform on your form and link it by PupilID.

 

[123James]

Thats exactly what i have done! the only problem is staff will not always be entering data when a new pupil is added yet i still want the PupilID to appear in the grades tables, even if there are no grades for that pupil. For this to work something MUST be entered in the subform. Consequently i created a 'Confirm New Pupil' field on the grades tables that an admin staff would click when a new pupil was added to ensure that the PupilID appeared on the grades tables. This works fine but is messy and i was hoping there was another way of doing it.

Thanks for your help.

 

[Rich]

The most reliable way is to run a query that looks for missing child records, the other way requires code at form level

 

[KeithG]

I don't see why you would want to enter the PupilID if they don't have any grades yet. You could create a query with a left join on the PupID field from your names table to your exam grades table (with names table on the left side). This will show every record from the names table.

 

[123James]

unfortunately creating a query that way does not work! i have tried it many times. unless the pupilID is already in a record on the grades table you cannot link the PupilID to a Grades record through a query.

the reason i need the PupilID to appear in the grades tables even if the student has no grades is this: The teachers enter the grades for their students. To enter the grades they access a data entry form. this form displays each student they teach and blank data boxes where they enter the grades. The form is set to CONTINUOUS to display all their pupils and therefore will not accept a subform (as Keith suggested earlier)!?!?

HELP!

 

[KeithG]

Can you post your db so I can have a look?

 

[123James]

Thanks Keith that would be great. I will try and get an edited replica of it online for you to see by tommorrow. i am unable to put it online now as it contains alot of sensitive data protected by the DPA. im sure you understand.

...Plus it is HUGE in size!

James