Minimum of Monthly Averages

[JasonM]

Good afternoon all!
I am wondering if I am going about my problem incorrectly, perhaps someone here can offer some advice to get me back on track...

I have a report which is grouped by year and by month. I am displaying monthly averages for three fields as well as annual average for each of 5 years.
In my "Year" footer I am displaying the annual average, annual minimum and annual maximum. However Access takes the daily minimum and maximum, and those numbers just don't appear in my report. What I would like is to take the minimum monthly average in the year footer.
Can I do this without creating a query that already summarizes the data by month?

 

[Trevor G]

Welcome to the Forum Jason,

Just checking passed threads and noticed yours, have you found a solution as yet? If not (if your allowed) could you strip down a copy of the database and attach to your thread it will help in finding a solution.

Give names of relevant tabels and the report.

Maybe useful to state which version of Access you are using, as there maybe different approaches.

 

[JasonM]

Trevor, thanks for the reply. Here is some of the requested info.

I am currently working in Access 2003. The screen shot tries to illustrate what I was asking...

The Query SQL is as follows:

qryDisinfectionProfile_1
BEGIN SQL
SELECT CT_ALL.CTDate, CT_ALL.C, CT_ALL.T AS PlantTime, IIf([t]=153.3,30,IIf([T]=104.7,19,IIf([T]=78.2,13.5,IIf([t]=319.8,67,IIf([t]=376,67,IIf([t]=188,30,IIf([t]=125,13.5,IIf([t]=94,13.5,[t])))))))) AS ClearwellTime, nz([Sed_Cl2],monthlyReport.MC12) AS MC12, IIf([t]=153.3,123.3,IIf([T]=104.7,85.7,IIf([T]=78.2,64.7,IIf([t]=319.8,252.8,IIf([t]=376,252.8,IIf([t]=188,123.3,IIf([t]=125,85.7,IIf([t]=94,64.7,[sed_t])))))))) AS SedBasinTime, MonthlyReport.MPh, ([EFF_TEMP]-32)*0.56 AS EffTemp, CT_ALL.pH, CT_ALL.Temp, CT_ALL.T, CT_ALL.Sed_T, CT_ALL.Sed_Cl2
FROM MonthlyReport INNER JOIN CT_ALL ON MonthlyReport.MRDate = CT_ALL.CTDate
GROUP BY CT_ALL.CTDate, CT_ALL.C, CT_ALL.T, IIf([t]=153.3,30,IIf([T]=104.7,19,IIf([T]=78.2,13.5,IIf([t]=319.8,67,IIf([t]=376,67,IIf([t]=188,30,IIf([t]=125,13.5,IIf([t]=94,13.5,[t])))))))), nz([Sed_Cl2],monthlyReport.MC12), IIf([t]=153.3,123.3,IIf([T]=104.7,85.7,IIf([T]=78.2,64.7,IIf([t]=319.8,252.8,IIf([t]=376,252.8,IIf([t]=188,123.3,IIf([t]=125,85.7,IIf([t]=94,64.7,[sed_t])))))))), MonthlyReport.MPh, ([EFF_TEMP]-32)*0.56, CT_ALL.pH, CT_ALL.Temp, CT_ALL.T, CT_ALL.Sed_T, CT_ALL.Sed_Cl2
HAVING (((CT_ALL.CTDate)>=#1/1/2002#))
ORDER BY CT_ALL.CTDate DESC;
END SQL
This query sets up the needed fields, does some of the preliminary math and sets the date range...
qryDisinfectionProfile_2
BEGIN SQL
SELECT qryDisinfectionProfile_1.CTDate, qryDisinfectionProfile_1.C, qryDisinfectionProfile_1.PlantTime, qryDisinfectionProfile_1.ClearwellTime, [c]*[ClearwellTime] AS ActualClearwellCT, (3*([C]*[ClearwellTime])/IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*[C])+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*[C])+(0.361*[ph])))))) AS ClearwellLogRemoval, IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*[C])+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*[C])+(0.361*[ph]))))) AS ClearWellCT, qryDisinfectionProfile_1.Temp, qryDisinfectionProfile_1.pH, IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*([MC12]*0.2))+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*([MC12]*0.2))+(0.361*[ph]))))) AS SedBasinCT, qryDisinfectionProfile_1.MC12, qryDisinfectionProfile_1.SedBasinTime, [mc12]*[sedbasintime]*0.2 AS ActualSedBasinTime, 3*(([mc12]*0.2)*[SedBasinTime])/IIf([Temp]<=12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*([mc12]*0.2))+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*([mc12]*0.2))+(0.361*[ph]))))) AS SedBasinLogRemoval, 3*(([mc12]*1)*[SedBasinTime])/IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*([mc12]*1))+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*([MC12]*1))+(0.361*[ph]))))) AS SedBasinLogRemovalNoReduction, (3*(([MC12]*1)*[SedBasinTime])/IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*([MC12]*1))+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*([MC12]*1))+(0.361*[ph]))))))+((3*([C]*[ClearwellTime])/IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*[C])+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*[C])+(0.361*[ph]))))))) AS TotalRemovalFullSedBasin, (3*(([mc12]*0.2)*[SedBasinTime])/IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*([mc12]*0.2))+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*([mc12]*0.2))+(0.361*[ph]))))))+((3*([C]*[ClearwellTime])/IIf([Temp]<12.5,((0.353*3)*(12.006+2.7183^(2.46-(0.073*[temp])+(0.125*[C])+(0.389*[ph])))),((0.361*3)*(-2.261+2.7183^(2.69-(0.065*[temp])+(0.111*[C])+(0.361*[ph]))))))) AS TOTALreduction, Format([ctdate],"yyyy") AS CTYear, Format([ctdate],"mm\,yyyy") AS CTMonth, 0.788 AS Benchmark
FROM qryDisinfectionProfile_1
END SQL
This one is a bit uglier as there is a fair amount of math being performed... I do this so that I can export it to excel if needed by our regulating agency.
Again, what I would ideally like to do is display the minimum MONTHLY average in the year footer, NOT the minimum DAILY value...

Thanks for any pointers, and sorry this is so long. It seemed easier than trying to strip the data from the db...

 

[Trevor G]

This does seem a complicated query (SQL) James,

I suppose I would ask what is behind the Field (Source) in the Footer and what would you expect it to show.....

 

[JasonM]

The footer simply uses the average function =min([field name])
I cannot see any way with how the report is constructed that will allow for that. Perhaps I can create a crosstab query from qryDisinfectionProfile_2 to group the needed data by month... That way I would only have "one" result per month then the min() function would provide one of the results from the previous 12 months...