6 Replies Latest reply: May 15, 2014 10:07 AM by Set_Shot _ RSS

    Removing Dupes

    Set_Shot _

      Good Day All,

      I have a tricky situation. Here is my set-up.

      Within a given Class I may have duplicate students, this is cause by a test taken more than once. My problem is how can I get rid of duplicated record within a class. I can not get rid of duplicated record in a whole since a student Can take more than one class.

       

      Thanks

      Set_Shot

        • Removing Dupes
          Olly Bond

          Hello Set Shot,

           

          You'll be able to do this in the table using a filter.

           

          Define a new filter, and don't bother entering an expression in the dialog box, click straight on the Advanced tab. There, select the option to show one row only for duplicates, and check the Class and Student fields. That way, any records which have the same student in the same class will be de-duplicated.

           

          If you then need to group the records by Class, you might want to use a summary, and you can just set this summary to use the advanced filter you've just created.

           

          HTH,

           

          Olly

            • Removing Dupes
              Set_Shot _

              Good Afternoon Olly Bond,

              I enter the above metioned suggestion, one problem it's getting rid of the duplicate(s) all together. I still need one of the records to remain. Say I have two Joe Smiths

               

              XXX-XX-9999   Joe Smith        test score_1

              XXX-XX-9999   Joe Smith        test score_2

               

              I will need to keep the last line of detail.

                • Removing Dupes
                  Olly Bond

                  Hello Set Shot,

                   

                  As long as it's always the last line you need to keep, then you might be able to solve it with Recno(). Define a calculated field as Recno(), and then sort on in descending order.

                   

                  Then define a filter picking the unique entries by class and student, and the selected entry for duplicates should be the one you're looking for.

                   

                  Best wishes,

                   

                  Olly

                    • Removing Dupes
                      Set_Shot _

                      Thanks, But now that I look at the data more closely the last dupe is not alway the one we want to keep. The best case would be to de-dupe the detail line that does not generate a postive number, or better yet the greater number.

                       

                      XXX-XX-9999 Joe Smith test score_1  80.0

                      XXX-XX-9999 Joe Smith test score_2  65.0

                       

                      In this case I would like to keep the first line.

                       

                      XXX-XX-9999 Joe Smith test score_1  30.0

                      XXX-XX-9999 Joe Smith test score_2  65.0

                       

                      In this case I would like to keep the second line of detail.

                       

                      I guess I would need to apply a formula where in the case of a duplicate. Test for the best test score and keep that detail line.

                       

                      Hopefully I'm explaining it correctly.