Results 1 to 6 of 6
  1. #1
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118

    Averaging fields in a report by date


    I have a report that lists miles for each trip in a day. Any date may have 1 trip, or 2 or 3. I'm trying to get the average of miles by date. In the report embedded on the DataEntry form I want to show the average miles per day as well as total miles. I can easily average all the miles but can't figure out the average for miles per day. For the dates of 7/15 thru 7/19 the average of all miles is 2.54 but by date it should be 4.15. How do I get the average miles bay date? See attached DB
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the report DATE group footer, put a text box : =([TotalMiles]/[VehicleCount])

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see this a lot.

    After looking at your dB, it seems to me that you do not understand the difference between Time and Duration.

    A Time has colons in it - half past one can be/is written as 1:30:00.
    If you say "It took us 1 hour and 30 minutes to walk 3 miles" , you can write it as 1 hour and 30 minutes or 1.5 hours.

    You cannot write it as 1:30:00.


    You cannot take miles and divide it by a time. You MUST divide it by a duration. Opening the form "Daily DetailRpt", you see

    Miles
    Daily Time
    Time 2
    Pace
    820.53 137:13:23 103:38:54 0:17:41

    The total miles is ok.

    I don't know what "Time 2" and "Pace" represents, but somehow you need to get the duration of the walks. And I would rename "Daily Time" as "Duration".
    If "Pace" is MPH, you need to divide the miles by the time (duration) it took to go the number of miles.

    What time is 137:13:23? Because it is definitely not a duration.

  4. #4
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Sorry about the delay in responding.
    ranman265--I don't know where the field [VehicleCount] comes from. It's not a field in the database

    ssanfu--These are durations of time 137:13:23 is hours, minutes and seconds. It is the sum of all the individual trips. However my question doesn't involve any time. I'm trying to get the average miles by date over a period of dates. Take the dates of the 18th and the 19th. A total of 3 trips. 2 on the 18th and 1 on the 19th. The average of those 3 trips is 2.62 miles, however the average of the miles per day is 3.94. 3 miles on one date and 4.88 miles on the other date. That's the calculation I'm trying to build on a report.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps this is what you want?

    Code:
    SELECT Activity.CurrDate, Count(Activity.CurrDate) AS CountOfCurrDate, Sum(Activity.Miles) AS SumOfMiles, Sum([miles])/Count([currdate]) AS Expr1
    FROM Activity
    GROUP BY Activity.CurrDate
    gives the result
    CurrDate CountOfCurrDate SumOfMiles Expr1
    3/18/21 2 4.98 2.49
    3/19/21 1 3 3

    note you say 4.88 for the 18th, presume that is a typo

  6. #6
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Ajax, That worked for me and I learned something new. Thanks to all who responded

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

Similar Threads

  1. Averaging calculated time fields
    By JonathanT in forum Reports
    Replies: 22
    Last Post: 08-17-2018, 04:10 AM
  2. Replies: 3
    Last Post: 03-20-2015, 08:36 AM
  3. Averaging fields with missing data
    By mfred1 in forum Queries
    Replies: 2
    Last Post: 03-19-2015, 08:32 AM
  4. Averaging one column in a report
    By FranCorona in forum Reports
    Replies: 7
    Last Post: 08-02-2013, 03:42 PM
  5. Date Range Report with Multiple Date Fields
    By StevenCV in forum Reports
    Replies: 13
    Last Post: 02-29-2012, 08:29 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