2 Replies Latest reply: May 15, 2014 10:05 AM by Grant Perkins RSS

    Max Key/Item Fields Summary View

    rok _

      Monarch Pro 8.02

       

      I am creating a Summary View of a Table I have because I need to sum the balance columns based on a given ID

       

      Example Table View:

       

      ID1 $25

      ID2 $35

      ID1 $10

      ID3 $40

      /code

       

      Example Summary View:

       

      ID1 $35

      ID2 $35

      ID3 $40

      /code

       

      The problem is, I have a huge number of key fields that I would also like included, like name, address, phone numbers, etc (which everything is the same except the balances).

       

      It appears Summary View will only allow a max of 30 key & items fields.  Any workaround?

       

      I'm not sure what other info is needed, thanks for taking the time

       

      rok

        • Max Key/Item Fields Summary View
          Data Kruncher

          Hi rok,

           

          Just how many is "huge number"?

           

          You may need to build a two model (at least) solution to accomplish what you need.

           

          In the first model, define a series of summaries to include all of the key and item fields that you need; just be sure to use the ID # as the first key field for each summary. You can build an unlimited number of summaries in a model.

           

          Then build a project export to build a single Excel file (or Access db) to export all of the summaries in one pass, so long as each summary goes to its own table (Excel sheet). Save your model and a project file.

           

          Now build another model, connect to the Excel file/Access db as a database. You can build up to nine external lookups with v8 Pro, iirc, to bring in data from each of the other tables.

           

          If you still need more lookups, export the table that you've got so far, and then repeat the process using this latest export as the primary input, then build more external lookups. Etc., etc.

           

          Just be careful what you name things. Maybe include "Pass 1", "Pass 2" ... "Final Data" in the names used.

           

          Make sense?

           

          Kruncher

            • Max Key/Item Fields Summary View
              Grant Perkins

              ok,

               

              Can I ask you why fields like name, address and phone number need to be key fields? At that level of detail (and for many databases with the likely level of input error involved with such records) any attempted summarization coould be very suspect.

               

              If you really have a need to summarize by those key fields, all at the same time, then so be it but is would be unusual and quite possibly, as I have found in the past, unhelpful.

               

              If you just need to add the in information as 'items' rather than key fields for calculations it might be better to create one or more extracts for your 'real' primary keys as Data Kruncher has suggested, perhaps using Memo fields to present the information as a block rather than in separate fields.

               

              Or you could run the inverse of the process - do the calculations and export the results as a lookup table and then pull them back into the individual records in your table.

               

              The other consideration is that once you get a large number of key fields the number of calculations Monarch has to perform for each drill level becomes very large and, naturally, performance may be impacted - noticably for very large databases.

               

              HTH.

               

               

              Grant