5 Replies Latest reply on Aug 2, 2016 7:27 AM by velistar

    Duplicates Find

    dgreensp_1

      I have a database with the following fields:

      Company Name

      Employee Name

       

      I want to find all the duplicate employee's that work at a particular company.  For example:

       

      I want to find all the Mike Smith's that work for Apple.

       

      I'm currently doing a find with Apple in the Company Name field and ! in the Employee Name field, but it is not giving me just the duplicates.  Any suggestions?

        • 1. Re: Duplicates Find
          Mike_Mitchell

          One method is to create a self-joining relationship on both of those fields. You can then use Count ( ) to determine how many records are in the related set.

           

          Another method is to create a concatenated field, such as:

           

               Company Name & "-" & Employee Name

           

          Then look for duplicates on that.

          1 of 1 people found this helpful
          • 2. Re: Duplicates Find
            fmpdude

            I think you meant you had a "table" with the referenced fields.

             

            -----------

             

            To find all the Mike Smiths who work for Apple, try this:

             

            Select * from <table_name> where employee="Mike Smith" and company = "Apple".

             

            ----------

             

            To find duplicates, you can use this SQL idea (group by having count(*) > 1):

             

            Select * from <table_name> group by <fields> having count(*) > 1

             

            ----

             

            Regarding using "!" to find duplicates...

            If I have a table like this:

             

            And then do a Find with "!" in both company and employee fields, I get:

             

            How does that differ from what you're doing?

             

            ----

            Though if you want to find all the Mike Smiths who work for Apple, I'd just use a Find:

             

            Perform Find, gives:

             

            What you want, no?


            HOPE THIS HELPS

            1 of 1 people found this helpful
            • 3. Re: Duplicates Find
              David Moyer

              Hi,

              when I tried what you described, it worked for me.

              Are the two fields in the same table?

              And are you using a single request?  That is, Apple AND duplicate names, as opposed to OR (two requests).

              Addendum:

              Now I see it ... if there's one John Doe at Apple and another at IBM, John Doe will come up in the result.  Is that the issue?

              • 4. Re: Duplicates Find
                dgreensp_1

                I used the concatenated field and looked for duplicates.  Easiest solution.  THANK YOU ALL!!

                • 5. Re: Duplicates Find
                  velistar

                  This method is also good in cases that you want to test record uniqueness for say patient records but you may get patients with the same name and surname. This way you can concatenate the full name and birthday. If you get two folks with exactly the same full name and birthday well then might as well pay a visit to your nearest Casino