4 Replies Latest reply on Jul 8, 2009 11:17 AM by mrvodka

    Unique and Repeat Record Issues

    Jerten81

      Title

      Unique and Repeat Record Issues

      Post

      I have a database where I look at sales transactions over a given time period with the following fields: Customer, Product Purchased, Date Purchased.  I want to be able to isolate only repeat customers during the month of June.  For example's sake let's say there were 20,000 sales in the month of June and 15,000 of those sales were one time customers.  I want to take an in depth look at the other 5,000 sales and figure out information such as: how many customers were responsible for those 5,000 purchases, how many customers bought two items (three items, four items etc.).  I can't seem to get my head around the best way to do this.  The unique record functions don't help me because they only tell me how many "unique" customers bought something in the month of June which is not what information I'm trying to get.  Any help/suggestions would be much appreciated.  Thanks.

        • 1. Re: Unique and Repeat Record Issues
          mrvodka
            

          First I would create a new calculation that returns a generic month and year for reporting such a:

          cMonthYear = Date ( Month ( SalesDate ); 1; Year ( SalesDate ) )

           

          Now create a self join keyed on the fkCustomerID = fkCustomerID AND cMonthYear = cMonthYear.

           

          You can also create another calculation to figure out how many records a customer has for a particular month.

          cMonthYearCount = Count ( SelfJoin::pkInvoiceID )

           

          Now you can do a find for the current month in the SalesDate field for anything > 1 in cMonthYearCount.

           

          • 2. Re: Unique and Repeat Record Issues
            philmodjunk
              

            Here's a hint to get you started:

             

            If you enter into find mode and place a ! by itself in a field. You will be able to find all records that contain duplicate entries in that field. Sort on that field and you will have successfully grouped the records by the duplicate values.

             

            Another hint:

            You can create subsummary reports without any "body" part. Your sub-summary can then hold summary fields that do aggregate/statistical functions to produce counts, averages, etc.

            • 3. Re: Unique and Repeat Record Issues
              Jerten81
                

              Thanks for the help!  This works great!  Now is there a way to use a summary or calculation field to tell me the specific number of repeat customers I have?

              • 4. Re: Unique and Repeat Record Issues
                mrvodka
                   Well one way to do it is after you find all the invoice records that match your criteria, you can use a Go to Related Records back to customers table ( show related only, match found set ).