5 Replies Latest reply on Jul 30, 2009 5:57 PM by Infectiologue

    Live table with summarized data (Monthly summarized in Quarterly)

    Infectiologue

      Title

      Live table with summarized data (Monthly summarized in Quarterly)

      Post

      What I would like is a table that summarizes monthly data by quarter, so I can work with it directly.  I would like it to update on the fly when data is entered in the monthly table. 

       

      I have a table of monthly results for a series of locations.

      For example, Month, location, infections,Cases,quarter_name(calculated from month)

       

      I sometimes need to analyze this data as quarterly results.

      For example, Quarter, location, infections, cases.

       

      No problem making reports of the data from Monthly with summary fields.

      No problem exporting these reports to other programs.

       

      I have partial success by making a relationship by (quarter_name and location) and using aggregate function SUM() in the quarterly table of the parallel field in the monthly table. Now if I put in a quarter an a location, the aggregate fields populate with the sum of the 3 months of infections or cases. 

       

      How can I get the Quarterly to automatically add records when Monthly contains data for that quarter? (The parallel solution would relate to daily/weekly/yearly).

       

      There must be a database trick I'm missing . . . thanks in advance for educating me.

       

      Mark

      Rochester, NY 

        • 1. Re: Live table with summarized data (Monthly summarized in Quarterly)
          philmodjunk
            

          You shouldn't need to create separate tables just to group your data by quarter instead of monthly, (or yearly, weekly etc.).

           

          You should be able to do this all from the same table and this will eliminate the need to "automatically add records" to the quarterly table as you won't even have such a table in your database.

           

          Create a summary report with sub summary parts that are visible when the records are sorted by Quarter_name.

           

          Use summary fields to summarize your data instead of the sum function.

           

          Place the summary fields in your sub-summary part to get subtotals by quarter.

          • 2. Re: Live table with summarized data (Monthly summarized in Quarterly)
            BatchMaker
              

            So this report is in browse mode, list view?

            If so, I think what your doing is a search for the current quarter and this is your new found set.

            A new record would be left out of the search, and therefore, out of the current found set.

             

            IF this is the way your report is structured, I have found success with a script step called "Install OnTimer Script". It runs a script at a given interval until told not to. I believe it is a new script step to FMP10, but I'm not sure.

             

            With this, you can renew your found set every 60 seconds and have up to the minute info instead of up to the second.

            • 3. Re: Live table with summarized data (Monthly summarized in Quarterly)
              Infectiologue
                

              Thanks for the reply. I am able to do summary reports and to export the summarized data to another program. I'm looking for a more robust solution, so that I can work with the data as quarterly data. 

               

              For example, if I want to search for all the quarters with more than 2 infections, the sub-summary fields will not work for me.  

               

              Any other ideas?

               

              • 4. Re: Live table with summarized data (Monthly summarized in Quarterly)
                comment_1
                  

                Infectiologue wrote:
                How can I get the Quarterly to automatically add records when Monthly contains data for that quarter?

                You can't, at least not automatically every time a new Monthly record is created*.

                 

                You can, however, do this on demand: show all records in the Monthly table, go into the Quarterly table and import records from Monthly, mapping QuarterName to QuarterName. You can either delete all Quarterly records before importing, or just validate the QuarterName field for Unique, Validate always.

                 

                 

                ---

                (*) Actually, you could do that too in version 10 with script triggers, but it wouln't be elegant.


                • 5. Re: Live table with summarized data (Monthly summarized in Quarterly)
                  Infectiologue
                    

                  Thanks! This was the hint I needed. 

                  I'll need to have a button that says "refresh quarterly results" which will delete the old info and repopulate with new.

                   

                  Because I have different locations and different quarters, I created a compositve of the two (Quarter and Floor) which I declared unique, otherwise it brought in duplicates.

                   

                   This forum is Great!