Your student records for students under 16 that have the same home address should all link to the same address record and thus there will be no duplicates to omit/remove. This would be a many to many relationship between parents and children.
O.K. Thanks for your reply Phil.
But does this mean I have to split the table? The children and addresses are all in one table. I don't have a separated parent table. Sometimes I have the same address more than once in the same table. (As well as mail addresses). That is because we don't need parent information further.
Yes, this data should be divided over several tables in order to avoid unnecessary duplication of this data.
I always split my tables as much as possible to avoid duplicate information. But in this case it doesn't make sense. We don't have further information of the parents (except the address and mail). But it isn't always working that way. Sometimes a student doesn't live with his parents. Sometimes also students over 16 wants the mail to be send to the parents. So, there is no "strict rule". Sometimes the parents are divorced, and the child lives with the mother, but the mail is going to the father. There is also a family with four children, two of them have their own mail address, and two of them use the mail address of the mother.
I have been struggling with this issue, and I have been thinking about splitting the table, but it seems it doesn't make sense.
There is a related table, a table with the ZIP codes, but it is not always the case that one address gets one mail. Sometimes, but sometimes not.
That's exactly why you need another table. What you don't want to have is the same address for the same family in more than one place. If that family changes their address, you have to track down and make identical updates to each instance of that data. This presents a real risk of allowing errors to creep into your database. Then the fact that you no longer have to omit duplicate addresses for your mailing tasks also disappears as you don't have any duplicates to omit
I've dealt with this issue a while back in a database used to manage contact info for a church children's ministry. A given child might easily be linked to multiple families all at the same time. Parents can be divorced or separated and the child might even be living with someone other than either parent. In our case, we needed the contact info for all of the above, but needed to know which address identified the "custodial" parent/guardian as that was the first choice contact info in case of emergency.
What you can set up looks something like this:
The address info would be entered into fields in Household. Any number of parent records (including no parent records) can be linked to Household and any number of Students can be linked to any one Household, but they can also be linked to other Households to document parents with different addresses and cases where the student does not live with either.
To manage that preferred address from the associated list, you can set up this relationship:
HouseHold|Preferred would be another table occurrence of Household.
If there is never more than one such address permitted or you can add a check box field in Student_Household to designate a preferred recipient if you want to allow a student to choose more than one location such as sending all communication to both divorced parents.
Thanks for your positive reply. I will figure this out, but ik will be a lot of work to split the tables. As you discovered as well, there are a lot of different family situation. Today they had three brothers at school, one is living with the mother, one is living with the father and one is living in an apartment with another student. For two children the mail goes to the mother and for the third one the mail is going to the father. (Seeing all this I am glad we have a stable environment for our children).
Thanks again Phil, I will work this out.