Subform within a Subform within a Mainform

[farner]

I'm using Access 2007.



This is my setup:

Tables: Students, Parents, Tuition, Payments, Faculty

My database is for a school that involves Students, Faculty, Parents, Tuition, and Payments. What I have is the Students listed under their Parents (they register as families), and the Payments listed under Tuition (so that it shows the list of payments and dates).

Each set of parents has a Guardian ID, and Students are listed under their Parents by Guardian ID. Tuition also has Guardian ID, and payments should be listed under Tuition by Guardian ID.

The issue I'm having is when I register a family (input a Parents record and the related Students), I need, on the same form, for a Tuition record to be made so that when I input the payments (Date of Payment, Payment Amount), the Tuition Table will have the list of payments underneath a Tuition record with the same Guardian ID as the related Guardian ID in Parents.

The Tuition Table is supposed to be the same as the Parents table, except rather than show students underneath each Tuition record, it shows the payments (from the Payments Table).

I have a form that allows for the input of the parent information, with a subform in it for inputting student info, linked to the main form through the Guardian ID. That works fine. What I'm having an issue with is including a second subform, which allows for input of payments. I want the payments to have the same Guardian ID as the current Parent record, as well as for them to show up under Tuition, which also has the same Guardian ID as the parent record, in order for it to all be for the same family.

What I did at first was put a subform for payments (continuous) inside a subform for tuition, and then plugged that subform into the main form. I linked the Payments and Tuition forms with the Guardian ID, and the Tuition and Main form with Guardian ID. The first time I did it, it worked great, except it wouldn't allow me to create any new Parent records. As I was trying to fix that (I just needed to select the Cascade Update and Delete options in the relationships - but I didn't find that out till later), I tried to replicate what I had done the first time, but it didn't work. Now I can't do it at all anymore, and I lost the first file while playing around (I should have saved and backed up the file). Everytime I input info for the Parents, Students, and Payments, the Parents and Students comes out fine, but the Tuition and Payments have no Guardian ID and are not linked. I don't know what to do.

This is the first time I've ever used Access, and so my knowledge of things is minimal. I'll be grateful for any help.

[June7]

Would have to look at the project to analyse issue. You can attach it to a post. Make copy and remove confidential data. Run Compact & Repair, zip if still large. The Attachment Manager is below the Advanced post editor.

[farner]

Thank you so much for taking the time to look into it. It's attached.

[June7]

Need some more information.
Will this database include multi-year data?
Will partial payments on tuition be allowed?

Initial observations:
1. Family Registration form RecordSource should be table Parents.
2. Students subform Master/Child links should be just GuardianID.
3. Do not save parent names in Students table, just the GuardianID key.
4. Do not save student age, just birthdate, calculate age.
5. Tuition and Payments tables do not have a relationship to each other. Each has a relationship to Parents.
6. Do not save TotalPayments, TotalTuition, Balance to tuition table. This is aggregate data and should be calculated by summing records. Quite possibly tuition table is not needed.

[farner]

It's just for a single year.
Yes, partial payments are allowed, and their sum is calculated in the PaymentSum query.

The sum of tuition for the students under each parent is calculated in the TuitionSum query.

1. & 2. : The RecordSource includes Faculty in order to include the student under their teacher's name when I need to make student rosters. That is also why it is linked by Faculty ID as well.

5. Would I then make a PaymentID field under Tuition and use that to link the Payments to Tuition, and then Tuition to Parents?

6. TotalPayments, TotalTuition, and Balance aren't actually saved, they're just left over fields from my previous attempts. All three are actually calculated under the Tuition - English report. I apologize for the confusion, I should have cleaned up.