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.