5 Replies Latest reply on Apr 15, 2011 8:20 AM by philmodjunk

    Consolidate records?

    MikeEdwards

      Title

      Consolidate records?

      Post

      What's a good way to consolidate records which have identical text values in specific fields?

      Thanks

        • 1. Re: Consolidate records?
          philmodjunk

          Do you need to combine data from the records or just delete the duplicates?

          • 2. Re: Consolidate records?
            MikeEdwards

            Combine data

            • 3. Re: Consolidate records?
              philmodjunk

              Sorry, should have asked these questions also:

              In the same table or are you merging this data into new records of a different table?

              What kind of data? Numerical data that should be summed? Most recent data overwrites data in older records or someother such rule?

              • 4. Re: Consolidate records?
                MikeEdwards

                Importing and updating Customer information has caused duplicate customer files. It is primarily numeric data for a monthly sales field.

                • 5. Re: Consolidate records?
                  philmodjunk

                  I think I still need to know more about how this data is structured and how you intend to use it.

                  Generally speaking, customer information and sales data should be imported into separate tables. The duplicates should be eliminated from the Customer table, but separate and new records for sales would then be kept in the sales table so that you can pull up reports that present the desired totals and sub totals. With such a table structure, there's no need to combine records unless you need to in order to get faster reports due to pulling together data from very large numbers of records, in which case a separate summary table can be created that "condenses" the data from multiple entries into a smaller set of "pre-calculated" totals for report purposes.

                  We use such a summary table here by condensing the line items records from our invoices into a separate summary table where each record records the day's total sales of a specific line item. 5 year comparision reports tracking monthly totals for a given item then can be produced with much fewer total calculations than trying to generate the same report directly from the line items table. We still keep all the line items records, however, so that we can pull up individual invoices and so that we can cross check totals to make sure that the script that summarized the data did so correctly.

                  That could be very different from what you need here. You'll need to let me know and then I can work from what you tell me.