sum fields based upon same ID number

[steely via AccessMonster.com]

Hi,

I would like to sum several fields in a table based upon an ID field and
use this calculation in a report. Can anyone give advice on the best way to
do this? For example I have about 200 records in a query table, some of
which have the same ID number. I would like to sum several fields in my
table when the site Id number is the same between records. So, for
instance, if three records in my table have a site id of 45, I would like
several fields at these sites to be summed in my report. Any specific
coding or descriptive information would be appreciated.....I am still a new
user of Access.

Thanks.

 

[Al Camp]

On your report, use the Grouping and Sorting dialog box to create a "break"
on the IDNo field. Enter the IDNo field in the grouping box, and just below
that... select yes for IDNo Header and IDNO Footer.
You'll now see an IDNo Header and Footer appear on your form... above and
below the Detail Section.
Place the IDNo field in the header...
Place the field/s you want to "sum" in the detail section (ex. field
[Score], or field [Price])
In the IDNo footer, place an unbound text control with a ControlSource of...
=Sum(Score)
Do the same for the other fields you want to sum.

Every time the report encounters a different IDNo, it will break... Print
the IDNo... list the items pertaining to that IdNo... and show the total of
those values in the footer. Then on to the next IDNo, and the next, and
etc...
hth
Al Camp

 

[Marshall Barton]

I would like to sum several fields in a table based upon an ID field and
use this calculation in a report. Can anyone give advice on the best way to
do this? For example I have about 200 records in a query table, some of
which have the same ID number. I would like to sum several fields in my
table when the site Id number is the same between records. So, for
instance, if three records in my table have a site id of 45, I would like
several fields at these sites to be summed in my report. Any specific
coding or descriptive information would be appreciated.....I am still a new
user of Access.

Use a Totals type query. FIrst, start a new query and
select the table. Click on the sigma button in the tool bar
or select Totals on the View menu to display the Total row
under the Table row.

Drag the ID field down to the field list, the Total row
should have Group By in it. Next, drag each field you want
to total down to the field list and select Sum in their
entry of the Total row. Run the query to see if you get
what you want.

As I understand your question, that should calculate the
desired values, but it is not a finished product. You'll
have to explain what/where/how you want to use those values
before we can take the next step.

 

[steely via AccessMonster.com]

Thanks for the help.

I now have a new totals query in place that totals the number of fish
surveyed (#fish) for every site that was surveyed (based on site_ID) for
all surveys. Now I would like to use this value in a report to calculate a
variety of other parameters based upon year and subbasin of the survey.
For instance, I would now like to sum up the values for #fish collected
based upon survey year and subbasin and then multiply this calculation by
the total amount of area in our sampling universe. I would like to get
these values to be calculated in separate text boxes in the report by
writing separate code in each box. My question is how do I do this? My
report presently has a control source set to another query as well. How
could I write code for each of these calculations in order to direct the
calculation to be made based upon a different query than what my report is
currently set to? Or should I use a different approach?

Thanks in advance.

 

[Marshall Barton]

I now have a new totals query in place that totals the number of fish
surveyed (#fish) for every site that was surveyed (based on site_ID) for
all surveys. Now I would like to use this value in a report to calculate a
variety of other parameters based upon year and subbasin of the survey.
For instance, I would now like to sum up the values for #fish collected
based upon survey year and subbasin and then multiply this calculation by
the total amount of area in our sampling universe. I would like to get
these values to be calculated in separate text boxes in the report by
writing separate code in each box. My question is how do I do this? My
report presently has a control source set to another query as well. How
could I write code for each of these calculations in order to direct the
calculation to be made based upon a different query than what my report is
currently set to? Or should I use a different approach?

I missed the part of your question about this being used in
a report. It sounds like you would be better off using Al's
idea of doing this in the report rather than in a special
purpose query.

If your report, based on your existing query, provides all
the needed data and you just need to organize it, then use
the Sorting and Grouping feature (View menu) to specify the
fields that define the groups you want to total. Set the
group footer property to Yes. Now, you can add a text box
to the new footer section and use an expression like
=Sum([#fish]) to get the total number of fish in the group.
This can be repeated for each level of grouping such as
SurveyYear and Basin to get the total for each (sub)group.
Additional text boxes in some (or all) of the footers can be
used to display other derived values using an expression
such as:
=Area * Sum([#fish])

 

[Al Camp]

Steely,
Check out Marshall's response... and...

You also wrote in the Getting Started NG...

I am trying make a calculation in a report based on another calculated
value in the same report. Can anyone tell me if this is possible? For
instance the first calculation in the report gives a calculation of
fish/per mile and I would like to multiply this calculation by another
number to a larger area. I tried to write the complex equation but received
an error message because of a variety of qualifiers used in the expression
(IIf[Year]=2005 and [subbasin]="PP].

It's really hard to be sure... but I think I can see where you are having
problems.

Your report calculations shouldn't involve any further filtering criteria
such as [SubBasin] or [Year]. You should just add those groupings to your
report, so the resulting values delivered by the grouping can be just added.
Relating to your previous post, you probably (only you can tell for sure
what the hierarchy of grouping should be) need to be grouping your report on
[Year], [IDNo], and [SubBasin], with associated =Sum(Fish) subtotals for
each group footer.
hth
Al Camp