Live table with summarized data (Monthly summarized in Quarterly)
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.