3 Replies Latest reply on Aug 8, 2014 8:50 AM by philmodjunk

    Embarrassed but still need the help…aggregate several records into one record

    JamesSAnderson

      Title

      Embarrassed but still need the help…aggregate several records into one record

      Post

           It has been a long time since I've done any database work. Any help would be greatly appreciated.

           I've got a database where there are several records where the key field is FullName. The records all have some information that is needed, yet it all needs to be in one record for exporting.

           Some of the fields need to be added together (a sum of numbers) when the fields are aggregated.

           Am I leaving anything out that is needed to help me?

      Screen_Shot_2014-08-08_at_10.10.02_AM.png

        • 1. Re: Embarrassed but still need the help…aggregate several records into one record
          philmodjunk

               I don't see a reason to actually produce a single record as it is very easy to keep these as separate records, but display aggregated data from a group of records in a single row.

               Define new fields in this table of type summary, one for each number field for which you need a total or other aggregate value computed from the group of records with the same FullName.

               Create a new layout based on this table and set it up for List View, not form or table view.

               Enter layout mode and double click the body part label to open Part Setup. Change the body part into a sub summary layout part. Select FullName as your "when sorted by" field.

               Put your fields in this sub summary layout part. Put your summary fields in this part instead of the number fields that they summarize.

               Enter layout mode and sort your records by FullName

               You'll get one row of data for each unique value in FullName (hope you don't have two people with identical names!) and the summary fields will display aggregate date based on the group of records with that name.

               Put the same summary fields in the header, footer or a grand summary layout part to show "Grand total" type values computed from the entire found set of records on your layout.

               You may find this tutorial on summary reports of interest: http://Summary Reports Reports, Summary

          • 2. Re: Embarrassed but still need the help…aggregate several records into one record
            JamesSAnderson

                 First, thank you for the very quick response. Second, before I go to setting up the new fields and layout, will enable exporting to an excel spreadsheet the aggregated single line item (using the summary fields)?

            • 3. Re: Embarrassed but still need the help…aggregate several records into one record
              philmodjunk

                   Yes. When you set up the export, there's a "group by" option that will allow you to export one row of data for each summarized group of records.