I have a database in acess that records monthly fleet maintenance and
fueling. I have a "Total" for each. I manually add the monthly numbers but
I want to automate this step as the fleet is getting bigger. How can I set
up the "Total" field to add up all those numbers in the "form" view?
A couple of ways. For starters, the total should NOT be stored in any
table; if you have a Total field in your table, you shouldn't! Instead
the total can and should be calculated on the fly.
One way is to use a Totals Query based on the table. Create a query
based on the table; select the field you want to total, and one or
more fields on which you want to group (e.g. you might want the total
for each vehicle in the fleet, or for each month in a date range).
Change the query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M). Leave the default Group By for the fields
you want to group by, and change it to Sum for the fields you want to
total.
On a Form, you can put a textbox in the form Footer with a control
source
=Sum([fieldname])
to sum the values of that field for the records displayed on the form.
The same method can be used on Reports, either in the report footer or
using the report's Sorting and Grouping feature to get multiple levels
of subtotals and totals.
