Hello everyone,
I am having some trouble with averages on a report I am working on.
The report has 4 pages:
Raw data - a line for each sample with the barcode, batch number, product & 2 different turnaround times (MIN TAT and MAX TAT) plus a bunch of other information
TAT by sample - a summary page with columns for the sample (grouped by sample), min TAT & max TAT
TAT by batch - a summary page with just the batch number (grouped), min and max TAT by batch number
TAT by product - a summary page with product (grouped) and min and max TAT by product
To get the average TAT's for the three summary pages, I used the average summary. I have then hidden the 'in-between' rows by setting box type to none. This works great for the different batches and products - the TAT's are accurate and I just get one line for each batch number / product / sample.
However, the problem I have is that the Overall Average in the summary line at the bottom on each page are wrong. It is the same across all 3 pages whereas it should be different for each page. I want it to show an 'average of the averages' but I think it is just showing an average from the raw data. For some date ranges it has even been really off - once it showed an average TAT of 3 days when none of the batches were less then 12 days.
I have tried making an individual query item for the batch number page to average out the TAT's by batch - this is what I have tried...
average ([IGEN_ORDER_TAT_MAXMIN] for [BATCHNUMBER])
(total ([IGEN_ORDER_TAT_MINMIN] for [BATCHNUMBER])) / (count( [BATCHNUMBER] for report))
The auto summary doesn't work and neither do the two queries above.
I would really appreciate any advice!! Is it even possible to get an average of the averages or am I going to have to make new queries for each page and join them together?? Currently everything is on one query.
Thanks in advance!