Help with totals in reports

[MelanieLHG]

Hi,

I am helping out in the voluntary sector and once they found out I had anything slightly to do with computers asked if I could create a database for them. It involves entering lots of figures, getting subtotals of these then a total of the subtotals

e.g say there were fields for the following items

cars
Vans
Lorries
bikes

I then created a report that showed four results from the Sum of cars, the Sum of Vans, Sum of Lorries, sum of bikes. How would I then go about getting one whole total of the sum of cars, vans lorries and bikes?

Any help would be grately appreciated

Thanks

Melanie

 

[dhookom]

Again, I think your table structure is not normalized. I would have a field named [VehicleType] which would store values like "car", "Van",...

However, if you have text boxes in a report or group header or footer, you should be able to add them together like:

  =txtCarSum + txtVanSum + txtLorrieSum + txtBikeSum

If any of these might be null, you need to use the Nz() function like:

  =Nz(txtCarSum,0) + Nz(txtVanSum,0) + Nz(txtLorrieSum,0) + Nz(txtBikeSum,0)

You can also reference the fields directly like:

  =Nz(Sum(Cars),0) + Nz(Sum(Vans),0) + Nz(Sum(Lorries),0) + Nz(Sum(Bikes),0)

Again, your table structure is probably not the best.


Duane
Hook'D on Access
MS Access MVP