Results 1 to 2 of 2
  1. #1
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110

    Summing group total of mileage

    I have a report for Employee Travel Log. One of the controls shows the odometer reading. The travel is grouped by employee and date. In the group footer I have a control that subtracts the minimum odometer reading from the maximum odometer reading, and this is their Daily Mileage. The next day they will have a new odometer reading at the start of the day.

    What I want to do is Sum all the Daily Mileages for the employee. I have tried setting a text box control in the employee group footer to = Sum([DailyMileage]) but it returns nothing.



    Any suggestions on how I can accomplish this?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It is doubtful you will be able to calculate the sum of mileage because the variant, Starting Mileage. I would create a query (don't think about anything other than Daily Mileage). In this query, create an alias the will subtract Min from Max. So you will need to include the date field and maybe the employee ID field. Make sure you have just enough fields in your query to get the correct math and also have a field to JOIN your new query as a subquery to the original.

    The tricky part is getting Min and Max. May require running these aggregates first and then bringing the results to your subquery.

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

Similar Threads

  1. Replies: 3
    Last Post: 05-05-2014, 10:52 PM
  2. Replies: 13
    Last Post: 03-20-2014, 09:31 AM
  3. Replies: 2
    Last Post: 03-28-2013, 05:59 PM
  4. Need help regarding forms and 'summing a total'.
    By JackCampion in forum Forms
    Replies: 2
    Last Post: 09-17-2012, 05:03 PM
  5. Group by Level2 BUT Total by Level3
    By goodguy in forum Reports
    Replies: 3
    Last Post: 10-05-2011, 08:54 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