Query re design of Guardian-Student data

[Kayleigh]

I have re-designed one component of my system due to design flaws. I am looking for some insights in the best way to design the user-interface to my clients' best benefit. I have drafted one version but I feel this has developed as a result of the relationships design rather than considering how it is best for my client.
If anyone can suggest a more efficient method to:
1) create the ParentList without requiring several crosstab queries
2) display the guardians on one form which can be modified (frmParents) and how a number of students can be linked to guardians. (Possible from relationships point of view but not sure how to implement on front end.)
3) how to add new guardians which are linked to students (assuming students are added before guardians)
4) how to find if two guardians share same contact/address details and set only one of details as visible

See my sample database enclosed.
Would appreciate any suggestions here!

 

[plog]

I think you should put forms aside and work on Reports. Forms are the last thing to work on with a database--Tables, Reports/Queries, then Forms. You need to prove you can get data out of your tables in the manner you need before you build ways to get data into them.

The way forms work is usually derived from the relationship their tables are built on. For your data I would have these tables:

1. MainMenu - this would have a button to Guardians and Students

2. Guardians - this would list every Guardian in tblGuardian, but not be editable. It would have a button at the top called Add which would open GuardianEditAdd to a new record. Then below it list every record in tblGuardian but not be editable it would have a Edit button next to each record which would open GuardianEditAdd to that selected guardian.

3. GuardianEditAdd - this would be based on tblGuardian and show just one record--similar to one of the forms you have now. It would have a continous subform based on and showing all the related records in jtblStudentGuardian. That subform would be editable and allow new entries. It would be made of 2 drop downs one to select the studentID and one to select the relationship id. At the top of the subform it would have a "Add Student" which would open StudentEditAdd to a blank record and next to each record would be a button that would open StudentEditAdd to the specifc selected student.

4. Students--similar to Guardians (#2 above) but based on tblStudents

5. StudentEditAdd--similar to GuardianEditAdd (#2 above) but based on tblStudents. This would have a similar subform to GuardianEditAdd but would allow you to select a Guardian and Relationship for the specified student.

Again though--Reports.

 

[Pat Hartman]

Even though you have the tables almost organized for today's world, your form is still assuming that a child has two guardians and they are called mother and father and they live together and the child lives with them and the child has the same last name. That might still be considered normal but it can't handle 100% of the situations you have to handle.

You need to add the address to the student or add a FK to the student table that points to the guardian with whom he lives. If there are two guardians and they live together, pick one.

Then think about the purpose of the form. If it will be used to locate the guardians for a child, it needs to use the student as the main form and the list of guardians as the subform. You can't mush the guardians because if the parents are divorced, the probably don't live together.

So essentially you have two main views of data. From the perspective of the student in which case the guardian is the subform and from the perspective of the guardian in which case the students are the subform.

 

[Kayleigh]

My question is regarding the address - it seems duplicate data to repeat for multiple siblings in the same family?

 

[Pat Hartman]

You can create an Address table and have each record link to the table. HOWEVER, you have to be careful about when you change an address that is shared by multiple users. For example a married couple share an address. They get a divorce. The husband calls to change his address, you have to find out if the address is changing for the entire family or just for him. If it is just him, you would add a new record to the Address table that only he points to.

For your purposes, you just don't have enough data to worry about but a compromise would be to keep the address in the guardian table and mark one of the guardians as "head of household". Then the children can have a FK that points to the "head of household" guardian and other adults in the household can also have a FK to that record. This adds a little complexity since the guardian table will have both the fields for an address which may or may not be null and a FK to a different guardian record. You'll have to set up your form so that it is an either/or situation. either you have a FK to a different guardian record OR you have address fields populated. Then when someone wants to change a guardian address, you need to do a query to see if any child or guardian is pointing to this address and find out if it is changing for everyone or just one person. Doable but a little complicated.

Option 1 with a pure Address table might be simpler to implement and it is certainly more normalized. Sit and think about it for a while.