4 Replies Latest reply on Jul 1, 2014 8:53 AM by beverly

    How to summarize one large closed realtor table

    slayden@msn.com
      1. I have a LARGE table of closed sales by REALTORS in my market
      2. I need to have a layout that goups and totals the closed buyers as one, and the close listings as another on the same page.
      3. Due to the fact that when a REALTOR is on one side, there are multipole realtors on the other side, so when I use sub-summaries, it creates MANY sub-summaries for "the other agents..."

       

      How can I filter for

      1. These are one agent's closed buyers and the total? AND
      2. These are one agent's closed sellers and the total?

       

       

      Thanks in advance

        • 1. Re: How to summarize one large closed realtor table
          Mike_Mitchell

          Not sure what you mean by "multiple realtors on the other side" - perhaps this is a join table? - but it sounds like you might benefit from one of two approaches:

           

          1) You could create a dedicated reporting table where you create the records for your report so that you just have a "realtor" field instead of going back to the parent table. Then through scripting, just populate that table (either temporarily or for historical purposes).

           

          2) Use a Virtual List technique to accomplish much the same task. Assemble the necessary values into a set of return delimited lists, then use calculated fields to display the values. (Summary fields will still work in this configuration.)

           

          But basically, if I'm understanding your question correctly, you need to flatten your report into a single-table configuration so that "realtor" comes from a single place instead of from two parents to prevent the multiple summary effect.

           

          HTH

           

          Mike

          • 2. Re: How to summarize one large closed realtor table
            slayden@msn.com

            It's one table.  Please see attached excel

             

            What I need to do is summarize on one layout

            1. Date range data where realtor acts as seller's agent
            2. Date range data where realtor acts as buyer's agent

             

            In my old language (Coldfusion) I'd query all data, then do a query of queries routine adding a where clause filtering for above.

            How do I do this in fmPro?

            • 3. Re: How to summarize one large closed realtor table
              Mike_Mitchell

              Same suggestion applies. Since you're trying to summarize the same record twice, you won't be able to use a standard summary report. You'll need to, in essence, duplicate each record so it can be separately summarized by agent (once by listing agent, once by selling agent). So I would probably use a Virtual List in this case. You could use ExecuteSQL to create each list, then just tack one onto the end of the other.

               

              HTH

               

              Mike

              • 4. Re: How to summarize one large closed realtor table
                beverly

                LOL. CF query of queries, gotta love it. Don't I wish FM had this. Nearest cousin is ExecuteSQL as Mike suggested.

                 

                Beverly