Unanswered: aggregate function of a query field used in another field of the same query
I have a very serious problem with an apiary database, that I want to build. I am attaching sample database and a picture of the query the way I would like to have it.
I have in a query the maximum values for bee family strength for a given period. A have the average total at the last row. I want to have another field where each maximum value is divided by the average total.
I tried all I can imagine but I had all the possible errors and even Access crashes. Please help me to solve this!
I created a query in Access 2003 that does the calculations you describe. I hope you can translate the logic of what I did into Access 2007. The database is attached if you want to test it. The fields are Bee Family, Period (date), and Bee Strength. The query calculates by month. Good luck with the bees.
SELECT Table1.[Bee Family], Format([Period],"mm/yy") AS MonthOf, Max(Table1.[Bee Strength]) AS MaxBeeStr, Avg(Table1.[Bee Strength]) AS AvgBeeStr, [MaxBeeStr]/[AvgBeeStr] AS Max_DivBy_Avg
GROUP BY Table1.[Bee Family], Format([Period],"mm/yy");
Last edited by JerryDal; 04-30-10 at 15:32.