Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

Query For Percent Of Total

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.
1 Solution
Patrick MatthewsCommented:
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
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.

mlcktmguyAuthor Commented:
I had no idea that was possible.  It worked perfectly.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now