6 Replies Latest reply on Aug 18, 2014 11:54 AM by lijnbach

    Deduplicate records and select series

    lijnbach

      Title

      Deduplicate records and select series

      Post

      Hello everybody,

       

      I developed a solutions for schools with some advanced function, it all works fine (also thanks to PhilModJunk).

       

      Now I am facing two problems, in relation to the same mail function.

       

      The first one is that all the children have a mail address. The older children, above 16 years, have their own mail address. But for the younger children, the mail address of the parents is used. But there are parents with two or three children at the same school. I don’t want the parents to get the same mail two or three times.

       

      Is there someone who has a smooth solution to “deduplicate” records with the same mail address?

       

      The second problem is that the Internet provider does not allow more than 50 addresses in one mail. Sometimes the school has to send over 400 mails to the children (or their parents). So I have to cut the a mail with 400 addresses in 8 pieces.

       

      Can someone help me out with these issues? (I have a solution, with all kind of calculations, but it is not working smoothly and it is not user friendly).

       

      Thanks in advance,

       

      Hans Lijnbach.

        • 1. Re: NoFields
          philmodjunk

               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.

          • 2. Re: NoFields
            lijnbach

                 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. 

                 Hans Lijnbach

            • 3. Re: NoFields
              philmodjunk

                   Yes, this data should be divided over several tables in order to avoid unnecessary duplication of this data.

              • 4. Re: NoFields
                lijnbach

                     O.K. Phil,

                     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.

                     Hans Lijnbach 

                • 5. Re: NoFields
                  philmodjunk

                       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:

                       Students-------<Student_Household>--------Household------<Parents

                       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:

                       Students>-----HouseHold|Preferred

                       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.

                  • 6. Re: NoFields
                    lijnbach

                         O.K. Phil,

                         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.

                         Hans Lijnbach.