subtotal group records in Access 2007

[MKMurphy]

My report is based on a query. In the details section I have two query
fields, price and quantity. I also have a calculated field, txtLineTotal,
that multiplies price times quantity. My records are grouped by Supplier
then Material then Destination. Everything works great. However, I also
need to subtotal the txtLineTotal per group. in Access 2003 I created a
txtSumLineTotalPerSupplier {or Material or Destination as needed that was the
=Sum(txtLineTotal)} and placed that in a Group Header or Footer section. In
2007 I set the Data propery Running Sum of the txtLineTotal to Over Group
instead of creating a txtbox equal to the sum of a field. Problem is, it is
not properly summing all the records in each group. I can't even find a
pattern of incorrectness. Also, does it matter if I place the Running Sum
field in the header or the footer? I have it in the Header for the Suppler
and Material, but the footer for the Destination.

thanks for sharing your wisdom!!

MKMurphy

 

[Allen Browne]

The running sum should work in the group header or footer in most cases, but
move it into the group footer until you get it working. There are cases
where the timing can be different (e.g. if it picks up a total from a
subreport.)

Make sure it's not the Page Footer section.

Set the Format property of the Running Sum text box to something numeric
(e.g. General Number or Currency.) If Access thinks it's text, the results
can be inconsistent.

Also make sure the expression always generates a numeric value. For example,
you must not use something that generates a zero-length string ("") or "N/A"
in some cases.

To help debug, perhaps you could turn off the Running Sum property for the
text box, set its Visible property to Yes, and add another text box that
collects the running sum value.

Another possibility is to place the expression in the query that underlies
the report, by typing something like this into the Field row in query
design:
Amount: [Quantity] * [Price]
Then check that the query understands this as a number (results are
displayed right-aligned like numbers, not left-aligned like text), and in
the report use:
=Sum([Amount])

 

[MKM]

turns out, we didn't need the data anyway! but, I still want to solve it.
Everything is doing what it should...numbers only, in the footer. Problem
is, and a big one that I neglected to mention in the first post, is that the
user enters a number in a textbox on Form1, clicks 'Run Report', and the
number is not only printed in the report header, but used in the calculations
in the detail section. In the report header I have txtFuelCharge, which
correctly = Forms!Form1.txtbox. Let's say it is .16 for now. The detail
section may have has 2 records, one with a weight of 100 and one with weight
of 150. I created a textbox named txtFreightCost =[weight]
*[txtFuelCharge], which correctly displays 16 and 24. Subtotaling the 100
and 150...no brainer with the subtotal feature. Subtotaling the 16 and 24 is
what stumped me. In the footer, I tried it two ways: =Sum([weight]
*[txtFuelCharge]) and =Sum([txtFreightCost]). It threw a parameter sort of
thing, asking me for the value in a text box , either the Fuel Charge or the
Freight Cost, I can't remember now. It's like it couldn't 'see' the value
that had been created and used earlier.

thanks,
MKM

The running sum should work in the group header or footer in most cases, but
move it into the group footer until you get it working. There are cases
where the timing can be different (e.g. if it picks up a total from a
subreport.)

Make sure it's not the Page Footer section.

Set the Format property of the Running Sum text box to something numeric
(e.g. General Number or Currency.) If Access thinks it's text, the results
can be inconsistent.

Also make sure the expression always generates a numeric value. For example,
you must not use something that generates a zero-length string ("") or "N/A"
in some cases.

To help debug, perhaps you could turn off the Running Sum property for the
text box, set its Visible property to Yes, and add another text box that
collects the running sum value.

Another possibility is to place the expression in the query that underlies
the report, by typing something like this into the Field row in query
design:
Amount: [Quantity] * [Price]
Then check that the query understands this as a number (results are
displayed right-aligned like numbers, not left-aligned like text), and in
the report use:
=Sum([Amount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My report is based on a query. In the details section I have two query
fields, price and quantity. I also have a calculated field, txtLineTotal,
that multiplies price times quantity. My records are grouped by Supplier
then Material then Destination. Everything works great. However, I also
need to subtotal the txtLineTotal per group. in Access 2003 I created a
txtSumLineTotalPerSupplier {or Material or Destination as needed that was
the
=Sum(txtLineTotal)} and placed that in a Group Header or Footer section.
In
2007 I set the Data propery Running Sum of the txtLineTotal to Over Group
instead of creating a txtbox equal to the sum of a field. Problem is, it
is
not properly summing all the records in each group. I can't even find a
pattern of incorrectness. Also, does it matter if I place the Running Sum
field in the header or the footer? I have it in the Header for the
Suppler
and Material, but the footer for the Destination.

thanks for sharing your wisdom!!

MKMurphy

 

[Allen Browne]

One workaround if you are stuck would be to move the calculation into the
report's source query. Type an expression like this into the Field row in
query design:
FreightCost: [weight] * [txtFuelCharge]

This gives you a field named FreightCost, and so the report footer can sum
it:
=Sum([FreightCost])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

turns out, we didn't need the data anyway! but, I still want to solve it.
Everything is doing what it should...numbers only, in the footer. Problem
is, and a big one that I neglected to mention in the first post, is that
the
user enters a number in a textbox on Form1, clicks 'Run Report', and the
number is not only printed in the report header, but used in the
calculations
in the detail section. In the report header I have txtFuelCharge, which
correctly = Forms!Form1.txtbox. Let's say it is .16 for now. The detail
section may have has 2 records, one with a weight of 100 and one with
weight
of 150. I created a textbox named txtFreightCost =[weight]
*[txtFuelCharge], which correctly displays 16 and 24. Subtotaling the 100
and 150...no brainer with the subtotal feature. Subtotaling the 16 and 24
is
what stumped me. In the footer, I tried it two ways: =Sum([weight]
*[txtFuelCharge]) and =Sum([txtFreightCost]). It threw a parameter sort
of
thing, asking me for the value in a text box , either the Fuel Charge or
the
Freight Cost, I can't remember now. It's like it couldn't 'see' the value
that had been created and used earlier.

thanks,
MKM

The running sum should work in the group header or footer in most cases,
but
move it into the group footer until you get it working. There are cases
where the timing can be different (e.g. if it picks up a total from a
subreport.)

Make sure it's not the Page Footer section.

Set the Format property of the Running Sum text box to something numeric
(e.g. General Number or Currency.) If Access thinks it's text, the
results
can be inconsistent.

Also make sure the expression always generates a numeric value. For
example,
you must not use something that generates a zero-length string ("") or
"N/A"
in some cases.

To help debug, perhaps you could turn off the Running Sum property for
the
text box, set its Visible property to Yes, and add another text box that
collects the running sum value.

Another possibility is to place the expression in the query that
underlies
the report, by typing something like this into the Field row in query
design:
Amount: [Quantity] * [Price]
Then check that the query understands this as a number (results are
displayed right-aligned like numbers, not left-aligned like text), and in
the report use:
=Sum([Amount])