Doing Sum of Multiple Fields

[Frustrated]

I am creating a report off of a querie that contains multiple fields that I
need to add together to get montly and yearly totals for, I knew how to do
this once upon a time but for the life of me can't recall how to do it.
Can someone give me a hand please?

 

[Jeff Boyce]

If you have "multiple fields" containing data that you need to "add together
to get monthly and yearly totals for", it sounds like you have a
spreadsheet, not a relational database table.

If you had amounts for each month in a relational database table, you'd use
a Totals query to sum DOWN, not across.

If you'll provide a bit more information about the underlying data, the
newsgroup readers may be able to offer suggestions that make your work
easier over all. Otherwise, you'll have to come up with a new field in your
query and "feed" it a formula something like:

YourField: Field1 + Field2 + ...

(and heaven help you if even one of those values is a null!)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Frustrated]

The fields are hours worked by volunteers in various endeavors,Office,
Community Cupboard, funding/resource dev, child development,
board/committees, promotions, special events, and other. The hours worked on
each day are entered for each volunteer. What is needed is to get the totals
for the month for all of the items and a yearly total.

 

[Jeff Boyce]

If your "fields" contain hours worked on a day in each of "various
endeavors", you have a spreadsheet. Ask yourself this question, "if I add a
new endeavor, will I need to add a new field?" If your answer is "yes", you
have a spreadsheet.

One possible table structure that would allow you to use the Totals query
Access offers would be:

trelVolunteerHours
VolunteerHoursID (probably an Autonumber field)
VolunteerID (who worked? this is a foreign key field from a table of
volunteers)
DateVolunteerWorked (a date/time field)
EndeavorID (this is a category, a foreign key field from a table of
endeavors)
HoursWorked (this records the number of hours worked, on this date, by
this volunteer, in this category)

With data organized this way, it would be a simple Totals query to find the
sum of hours worked, by category, by volunteer, for a specified date range
(month, year, ...).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/