Results 1 to 8 of 8
  1. #1
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74

    Averaging one column in a report

    I have a report that has these Headers: Model, Admin, IssueMiles, Turn In Miles, Total Miles, Avg Miles, Issue Hours, Turn In Hours, TotalHours, Avg Hours. I need to show an average of totals for that model type but only if nothing was entered in a field because sometimes no miles or hours are recorded and I don't know if that is going to be a problem.... I have unbound texboxes for the totals columns with =Sum([Turn In Hours]-[Issue Hours]) but I don't know the formula for bringing up an average for only specific model types or if it's possible. Thank you for help on this. Below is an example of what the outcome needs to be:

    Model Admin Issue Miles Turn In Miles Total Miles Avg Miles Issue Hours Turn In Hours Total Hours Avg Hours
    Buffalo 6000 4173 4435 262 168 17
    Buffalo 6004 2987 3027 40 1579 17
    Buffalo 7265 24901 25226 325 2436 2474 38


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am sure is possible, just don't know how easily.

    How did you arrive at 17 Avg Hours for the two Buffalo records? Why no Avg Hours for the third?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    Hey You! Happy Friday! That is how the report is in excel. Apparently, what they do now is export the report to excel and if there is no input on either miles and/or hours they highlight the totals cells for only that model and it gives the average at the bottom.....I told the supervisor in charge of this report that it may not be the same; we may have to have it calculate the average for every record and not only the ones that have no data input.....and that would be fine.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I still don't understand the Avg Hours result. I don't know what formula to use to arrive at that result.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    I guess 17 is the average of 38? I am not too sure of how that worked either....the way she showed me was she just highlighted the totals column only for that model and in the bottom status bar of excel it shows the average of what is highlighted.....for example: if I put 25, 68, 97 in three cells and highlight them in the bottom status bar it shows 63.333333 as the average so I would round up to 63. You know what I just went in excel and put 38 with nothing and your right that isn't correct....LOL Her ding dang report she sent me is obviously wrong.....I YI YI! Anyway, the way it is supposed to be done is how I explain my example. But she was just wondering if there was a way to get this data in access instead of having to export to excel and do the whole highlighting deal.....

  6. #6
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    I'm sorry, I didn't even verify if her data was even accurate.....I should have checked that.....

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What you seem to be describing is an aggregate GROUP BY (Totals) query. Access Help has guidelines on building aggregate queries.

    Also, can build a report using grouping & sorting with aggregate calcs in group footers. The report will allow display of detail records as well as summary calcs. Again, Access Help has guidelines for building these reports.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    Thank you so much for your help, I really appreciate it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Help Averaging A Pivot Chart
    By azeotrope in forum Reports
    Replies: 8
    Last Post: 06-26-2013, 10:33 AM
  2. Averaging with a blank field
    By Gabriel2012 in forum Access
    Replies: 3
    Last Post: 12-06-2012, 12:36 PM
  3. Odd Averaging Function Needed
    By Heatshiver in forum Programming
    Replies: 2
    Last Post: 03-20-2012, 08:26 PM
  4. Averaging Percentages in a form
    By DICKBUTTONS in forum Access
    Replies: 1
    Last Post: 11-18-2010, 01:22 PM
  5. Averaging with a conditional check
    By sarah in forum Queries
    Replies: 1
    Last Post: 10-05-2009, 05:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums