Query For Percent Of Total

Posted on 2009-02-23
Last Modified: 2012-05-06
I have a client in the trucking industry.  They record miles for every one of thier drivers.  Each milleage record includes the driver id (DriverID), the date of the miles(DateOfMiles), the state the miles were driven in(StateOfMiles), number of miles on toll roads(TollMiles) and number of miles on non-toll roads(NonTollMiles).

One of the reports that the user needs is a breakdown of the percentage of miles driven in each state.  The most basic example of the report, which is a summary for all states looks like this:

State      Non Toll Miles       Toll Miles       Total Miles         Percent
NY           59821                      0                  59821               75.294
NJ            2033                        0                    2033                 2.559
Totals       79450                      0                  79450

The percent on each line is the percent of the total miles driven that were driven in that state.  In this case this driver drove 75.294 percent of his miles in New York.

There is another version of the report that has the same information as above but for each driver.

I know I can do this by creating a temporary file of all of the state and or driver info and then plugging the pertinent total into each record but it feelslike I should be able to do this with a query.  However, I don't know how.

My query would have to pull the total miles to each state record, then calculating the percentage of total would be a simple matter.

My end query would have to look something like this:
NonTollMIles, TollMiles, TotMiles:NonToll + Toll, TotalMilesForAllStates, PCTofTotal:TotMiles/TotalMIlesForAllStates

I don't know how ot get the 'TotalMilesForAllStates' in there.  I can create a query that contains that information but there is no link to each record.  Any ideas on how this could be accomplished.
Question by:mlcktmguy
    LVL 92

    Expert Comment

    by:Patrick Matthews
    In your query, you could do something like this:

    SELECT m1.DriverID, m1.State, m1.NonTollMiles, m1.TollMiles, m1.NonTollMiles + m1.TollMiles AS TotMiles,
          (m1.NonTollMiles + m1.TollMiles) / z.TotInState
    FROM tblMileage m1 INNER JOIN
          (SELECT m2.DriverID, m2.State, Sum(m2.NonTollMiles + m2.TollMiles) AS TotInState
          FROM tblMileage m2
          GROUP BY m2.DriverID) AS z ON m1.DriverID = z.DriverID AND m1.State = z.State
    LVL 58

    Accepted Solution

    If this is for a report, use the figures of the report directly. For example:

    ---- detail ----
    {TotalMiles}     {=[TotalMiles]/[GrandTotal]}
    ---- report footer ----
    GrandTotal: {=Sum([TotalMiles])}

    I show the control source withing the curly braces. The report generator will create the total query needed to calculate the Sum(TotalMiles), and you can use the name of that control in the detail section to compute the percentage.

    This will work both for the overall report and for the driver report, without any change.

    LVL 1

    Author Closing Comment

    I had no idea that was possible.  It worked perfectly.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now