Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Question 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!
    Attached Thumbnails Attached Thumbnails JPG DBS.jpg  
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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.

    Code:
    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
    FROM Table1
    GROUP BY Table1.[Bee Family], Format([Period],"mm/yy");
    Attached Thumbnails Attached Thumbnails BeeQuery.bmp  
    Attached Files Attached Files
    Last edited by JerryDal; 04-30-10 at 15:32. Reason: grammar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •