Report totals? 1

[misscrf]

I have the following query as the control source for a report:

SELECT tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, tblCarCost.CurCostAmount, tblCarCost.intPrevMileage, tblCarCost.intMileage, IIf([txtCarCostType]="Gas",[intGallons],Null) AS MyGallons, tblCarCost.PKCarCostID, tblCarCost.FKCostType, 
IIf([tblCarCost]![intGallons]>0,[tblCarCost]![CurCostAmount]/[tblCarCost]![intGallons],Null) AS MyAvgGal, 
IIf([tblCarCost]![FKCostType]<>6,Sum([tblCarCost]![CurCostAmount]),0) AS CostNoBod, 
IIf([intGallons]=0,0,IIf([txtCarCostType]="Gas",Max([intMileage])-Min([intPrevMileage])/Sum([intGallons]),0)) AS MPG

FROM (tblCarStatus RIGHT JOIN (tblCarNum RIGHT JOIN (tblCarModel RIGHT JOIN (tblCarMake RIGHT JOIN tblCar ON tblCarMake.PKCarMakeID = tblCar.FKCarMake) ON tblCarModel.PKCarModelID = tblCar.FKCarModel) ON tblCarNum.PKCarNumID = tblCar.FKCarNum) ON tblCarStatus.PKCarStatusID = tblCar.FKCarStatus) LEFT JOIN (tblCarCostType RIGHT JOIN tblCarCost ON tblCarCostType.PKCarCostTypeID = tblCarCost.FKCostType) ON tblCar.PKCarID = tblCarCost.FKCar

GROUP BY tblCar.PKCarID, tblCarNum.intCarNum, tblCarStatus.txtCarStatus, tblCarMake.txtCarMake, tblCarModel.txtCarModel, tblCarCostType.txtCarCostType, tblCarCost.DtCostDate, tblCarCost.intGallons, tblCarCost.CurCostAmount, tblCarCost.intPrevMileage, tblCarCost.intMileage, IIf([txtCarCostType]="Gas",[intGallons],Null), tblCarCost.PKCarCostID, tblCarCost.FKCostType, IIf([tblCarCost]![intGallons]>0,[tblCarCost]![CurCostAmount]/[tblCarCost]![intGallons],Null);

I have 2 controls on the form:
MinMile=Min([intPrevMileage])
MaxMile=Max([intMileage])

The report has a grouping for the car and the following controls:
Mileage==IIf([txtCarCostType]="Gas",[MaxMile]-[MinMile],"")
CarGal=[MyGallons]
AvgMPG=MPG
AvCostPG=[CostNoBod]/[MyGallons]
CostNoBod=Sum([CurCostAmount])

This all works fine, but I need to have a report total to total all of the above items for the entire fleet. When I try to put a copy of these controls in the report total, I get a bunch of different errors. Div 0, overflow, etc.

Can someone help, please? This is the last thing I need to finish and this project is done!

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Why is this query a totals when it doesn't look like any fields are being totaled

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I'm sorry. I don't understand the question. In the query there is a field for DtCostDate, as the query has every cost for every car on every date there was a cost for a car.

The report runs off a form, where the user can pick 1 or more cars from a multi-select listbox (none= all) and then they can pick either:
a checkbox for year to date
a combo drop down to choose a specific month/year (filled by any month/years with data)
1 or two text box controls to fill out greater than or equal to, lesser than or equal to, or both for a range.

This groups the report on date range, and sums the data for each car.

Whatever the user choses, I need to totals for each car, which I have. I also need the totals for the "fleet" which is all cars chosen for the report, for the date range.

Does that make sense?

Please let me know if you need the code for the form that the report is launched from. I didn't think it was necessary, becuase it just builds a SQL from the source query, with a where tacked on the end for the car and date criteria built on the form.

I just can't figure out how to total the following numbers for the whole fleet:
Mileage= total mileage for all cars for the fleet for that time. taken as the max(intMileage) - min(PrevIntMileage) for each car for that time range.
CarGal= total gallons of gas purchased in time range for all cars (intGallons)
AvgMPG= Mileage/CarGal for all cars in fleet
AvCostPG= average gas cost type cost totalled for all cars per gallons for all cars
CostNoBod= total cost of the car without including any cost type of body work.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I missed the aggregates burried inside the IIf()s. I would have written:

IIf([tblCarCost]![FKCostType]<>6,Sum([tblCarCost]![CurCostAmount]),0)

as

Sum(Abs([tblCarCost]![FKCostType]<>6)*[tblCarCost]![CurCostAmount])

I would expect you need to use text boxes with running sums. For instance, if you have a text box in a car footer that calculates Mileage named "txtCarMileage". Set its Running Sum to Over All. Then add a text box to the Report Footer with a control source of:

=txtCarMileage

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Duane, have I ever mentioned how much I love you? I don't actually know you, but you are such a life saver. I will try this out and let you know how it goes. Thanks for the advice!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

This totally worked. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot