My data comes into the database from an outside source, grouped by Quarter for the first two years, and by year after that. I'm able to summarize data in the various footers (6 at present), for various time periods and criteria, as they're just straight vertical sums. However, when I reach the report footer, I can only summarize the vertical raw data fields that are not sums or averages within the queries, but cannot do averages of averages. Perhaps I'm approaching this wrong, maybe I should be recalculating the yearly sums (for the quarterly data) and counting the time periods to get an average, though an attempt at this before did not give the appropriate results. Example attached, showing the first two quarters, the formating and data is the same below, but once two years out is simply by year, instead of by quarter. As shown in the second attachment, which also includes the report footer with the erroneous drill summaries.
So the data giving me problems is the last two columns, the Average Drills per time period, and only in the report footer, summaries up to that point appear correct as far as I can tell. Unfortunately, the time periods are sometimes quarters and sometimes years, so I cannot merely do a linear average. What I need is the average drill requirements per year over the life of the project in the report footer. So somehow I either need to pull the average drill number for the year, out of the footer that calculates that value for the first two years, or I need to recalculate it in the report footer so that all of my values are in a consistent timeframe, ie: average drills per year, and then take the average of those numbers.
I believe I might be able to set this up as a subreport based on a summary query where I combine the quarters into years and then average the results, but I'm wondering if it's possible to do this within the existing report. I am unable to reference values in other subfooters as far as I can tell, so I'm unable to do a =Sum([groupfooter2].[sum of drills]) or something of the sort, as it doesn't recognize the footer referenced cell as existing for manipulation.
So the data giving me problems is the last two columns, the Average Drills per time period, and only in the report footer, summaries up to that point appear correct as far as I can tell. Unfortunately, the time periods are sometimes quarters and sometimes years, so I cannot merely do a linear average. What I need is the average drill requirements per year over the life of the project in the report footer. So somehow I either need to pull the average drill number for the year, out of the footer that calculates that value for the first two years, or I need to recalculate it in the report footer so that all of my values are in a consistent timeframe, ie: average drills per year, and then take the average of those numbers.
I believe I might be able to set this up as a subreport based on a summary query where I combine the quarters into years and then average the results, but I'm wondering if it's possible to do this within the existing report. I am unable to reference values in other subfooters as far as I can tell, so I'm unable to do a =Sum([groupfooter2].[sum of drills]) or something of the sort, as it doesn't recognize the footer referenced cell as existing for manipulation.
Attachments
Last edited:
