Calculating totals of fields in groups (Access 2003 beta)

[Randall Arnold]

I'm having a devil of a time trying to produce totals of recurring records
within a group. Specifically, I have a field holding the amount of sand to
be delivered to a job (typically 12, as in cubic yards). Records are
grouped by subdivision in the detail section, and I want to show the
subtotal of sand required for each subdivision as well as a grand total for
the entire request (encompassing all subdivisions to be delivered that day).

I thought running sum was what I needed, but that didn't produce the desired
results. Access Help was no help because I couldn't find examples that
covered my need (which I find very surprising).

I found an example of exactly what I want to do in the book Access 2002
Bible, page 678. Table 21-2 shows a total using the Sum function on a
certain field. The syntax is as follows:

=Sum(Treatment Price]+

The syntax looked strange (missing parenethisis) but I went ahead and
modified it to match my field as follows:

=Sum([Quantity]+

Not surprisingly, I got an error ("the expression you entered contains
invalid syntax"). I tried various permutations and none worked.

Essentially, I want the individual sand loads listed one after another in
the Subdivision group, with a subtotal after each group, and a grand total
at the end of the report.

Can anyone help? This is making me nuts...

Randall Arnold

 

[Randall Arnold]

I tried that first, and got "#Error" in the text box. That's why I started
trying other ideas... none of which work.

I saw someone else suggest Running Totals for this sort of need, but that
produced weird results. Some Runnign Totals were correct, and some were way
off. No logic to it that I could see.

Randall Arnold

You should be able to add text boxes to your Group and Report Footers. Set
the Control Source property to:
=Sum([Quantity])
Make sure the name of the text box is not also the name of the field. And,
you can't do this in a Page Footer.

--
Duane Hookom
MS Access MVP

I'm having a devil of a time trying to produce totals of recurring records
within a group. Specifically, I have a field holding the amount of sand to
be delivered to a job (typically 12, as in cubic yards). Records are
grouped by subdivision in the detail section, and I want to show the
subtotal of sand required for each subdivision as well as a grand total for
the entire request (encompassing all subdivisions to be delivered that day).

I thought running sum was what I needed, but that didn't produce the desired
results. Access Help was no help because I couldn't find examples that
covered my need (which I find very surprising).

I found an example of exactly what I want to do in the book Access 2002
Bible, page 678. Table 21-2 shows a total using the Sum function on a
certain field. The syntax is as follows:

=Sum(Treatment Price]+

The syntax looked strange (missing parenethisis) but I went ahead and
modified it to match my field as follows:

=Sum([Quantity]+

Not surprisingly, I got an error ("the expression you entered contains
invalid syntax"). I tried various permutations and none worked.

Essentially, I want the individual sand loads listed one after another in
the Subdivision group, with a subtotal after each group, and a grand total
at the end of the report.

Can anyone help? This is making me nuts...

Randall Arnold

 

[Randall Arnold]

Update: I just got the subtotals for subdivisions working, so ignore my
other response, but the "#Error" shows up in Page Footer (guess that's why
you mentioned that). Any idea how to show the grand totals for the entire
report? I'll try report footer.

Randall Arnold

You should be able to add text boxes to your Group and Report Footers. Set
the Control Source property to:
=Sum([Quantity])
Make sure the name of the text box is not also the name of the field. And,
you can't do this in a Page Footer.

--
Duane Hookom
MS Access MVP

I'm having a devil of a time trying to produce totals of recurring records
within a group. Specifically, I have a field holding the amount of sand to
be delivered to a job (typically 12, as in cubic yards). Records are
grouped by subdivision in the detail section, and I want to show the
subtotal of sand required for each subdivision as well as a grand total for
the entire request (encompassing all subdivisions to be delivered that day).

I thought running sum was what I needed, but that didn't produce the desired
results. Access Help was no help because I couldn't find examples that
covered my need (which I find very surprising).

I found an example of exactly what I want to do in the book Access 2002
Bible, page 678. Table 21-2 shows a total using the Sum function on a
certain field. The syntax is as follows:

=Sum(Treatment Price]+

The syntax looked strange (missing parenethisis) but I went ahead and
modified it to match my field as follows:

=Sum([Quantity]+

Not surprisingly, I got an error ("the expression you entered contains
invalid syntax"). I tried various permutations and none worked.

Essentially, I want the individual sand loads listed one after another in
the Subdivision group, with a subtotal after each group, and a grand total
at the end of the report.

Can anyone help? This is making me nuts...

Randall Arnold