Calculating a grand total for an unbound control in a report

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

I have a report for sales grouped by customer as follows

customerA itemA lbs sold $ sales
itemB lbs sold $ sales

customerB itemC lbs sold $ sales etc.....

The report automatically subtotals per customer for lbs and $ then grand
totals in the report footer.

We have some customers with rebates, returns, allowances etc... that we have
an adjustment to the $ sales # that I pull in on the query that is the source
for this report and it shows up under the customer subtotal then I have a
control box that calculates adjusted sales so for $'s sold we have

$ sales customer subtotal (automatic subtotal from access)
customer rebates, returns and allowances (pulled in to query from a returns
table)
adj $ sales ($ sales customer subtotal - customer reb...)

I then calculate #'s off to the right off of the adj $ sales number just
fine but when I try to calculate a grand total in the report footer I just
cant get it to work. When I try =sum([adj $ sales]) which is the exat
control name I use for my other calculations based on this number that works,
it prompts me for a value for adj $ sales and will not calculate a grand
total for my report. How should I approach this to get it to work, I've also
tried to use calculations to back into it for example using $ sales - the
customer rebates, returns and allowances field, but get the same thing.
 
Chances are that you are missing something tiny, perhaps an extra space, or
lack of 1,

=Sum([FieldName])

should work (and indeed does for other fields).
 
Doubtful, I've tried a number of ways from typing manually to clicking the
name in the query builder to have it automatically added to changing the name
of the control I'm wanting to total to just A and typing that in. Always the
same result.
Do report footers work under some different rules or something? This is
just one example, I can grand total manually anything that is directly from
the query but cannot grand total any calculated control I have in the report
footer.

Arvin Meyer said:
Chances are that you are missing something tiny, perhaps an extra space, or
lack of 1,

=Sum([FieldName])

should work (and indeed does for other fields).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Robbro said:
I have a report for sales grouped by customer as follows

customerA itemA lbs sold $ sales
itemB lbs sold $ sales

customerB itemC lbs sold $ sales etc.....

The report automatically subtotals per customer for lbs and $ then grand
totals in the report footer.

We have some customers with rebates, returns, allowances etc... that we
have
an adjustment to the $ sales # that I pull in on the query that is the
source
for this report and it shows up under the customer subtotal then I have a
control box that calculates adjusted sales so for $'s sold we have

$ sales customer subtotal (automatic subtotal from access)
customer rebates, returns and allowances (pulled in to query from a
returns
table)
adj $ sales ($ sales customer subtotal - customer reb...)

I then calculate #'s off to the right off of the adj $ sales number just
fine but when I try to calculate a grand total in the report footer I just
cant get it to work. When I try =sum([adj $ sales]) which is the exat
control name I use for my other calculations based on this number that
works,
it prompts me for a value for adj $ sales and will not calculate a grand
total for my report. How should I approach this to get it to work, I've
also
tried to use calculations to back into it for example using $ sales - the
customer rebates, returns and allowances field, but get the same thing.
Click to expand...


.
Click to expand...
 
Been experimenting some more, it seems to be an issue with Access. I made a
very small, simple table with 2 columns a name and a number then made a
report based on that, then had it total the number column. I can go to the
bottom and create a new control with =sum([number])/2 and it works.
However if I go to the detail and add a new control that says =[number]/2
and name it half I get the correct numbers in the detail, but when I go to
the footer and try =sum([half]) I get the prompt box and it does not work.
 
The Sum, Count, Avg, Min, and Max only work on FIELDS that are in the report's
record source. They do not work on the controls in the report.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I meant to add that you can get a sum of an unbound control by setting the
control to use Running sum (overall for a grand total) and then referring to
that control in another control in your report footer.

Set the control's source in the footer to something like:
=[txtRunningSumControl]/2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
You ought to be able to total like that in any grouping.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Robbro said:
Doubtful, I've tried a number of ways from typing manually to clicking the
name in the query builder to have it automatically added to changing the
name
of the control I'm wanting to total to just A and typing that in. Always
the
same result.
Do report footers work under some different rules or something? This is
just one example, I can grand total manually anything that is directly
from
the query but cannot grand total any calculated control I have in the
report
footer.

Arvin Meyer said:
Chances are that you are missing something tiny, perhaps an extra space,
or
lack of 1,

=Sum([FieldName])

should work (and indeed does for other fields).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Robbro said:
I have a report for sales grouped by customer as follows

customerA itemA lbs sold $ sales
itemB lbs sold $ sales

customerB itemC lbs sold $ sales etc.....

The report automatically subtotals per customer for lbs and $ then
grand
totals in the report footer.

We have some customers with rebates, returns, allowances etc... that we
have
an adjustment to the $ sales # that I pull in on the query that is the
source
for this report and it shows up under the customer subtotal then I have
a
control box that calculates adjusted sales so for $'s sold we have

$ sales customer subtotal (automatic subtotal from access)
customer rebates, returns and allowances (pulled in to query from a
returns
table)
adj $ sales ($ sales customer subtotal - customer reb...)

I then calculate #'s off to the right off of the adj $ sales number
just
fine but when I try to calculate a grand total in the report footer I
just
cant get it to work. When I try =sum([adj $ sales]) which is the exat
control name I use for my other calculations based on this number that
works,
it prompts me for a value for adj $ sales and will not calculate a
grand
total for my report. How should I approach this to get it to work,
I've
also
tried to use calculations to back into it for example using $ sales -
the
customer rebates, returns and allowances field, but get the same thing.
Click to expand...


.
Click to expand...
Click to expand...
 
The Sum, Count, Avg, Min, and Max only work on FIELDS that are in
the report's record source. They do not work on the controls in
the report.
Click to expand...

They don't?

My experience is that they certainly do, as long as there are
mutliple instances of the control referenced in a level lower than
where the aggregation is asked for.

If, so, you have a field txtInvoiceItemTotal in each detail, and you
have a group on Invoice, you can have a control bound to the
expression =Sum(txtInvoiceItemTotal).

I've done this for years.
 
Ok, I just tested this (Access 2003)

I have a control on the report in the detail section named txtSomeNumber which
is bound to a number field named SomeNumber.

I added three controls to the Report's Footer
txtA: Control source: =Sum([SomeNumber])
txtB: Control Source: =[txtSomeNumber]
txtC: Control Source: =Sum([txtSomeNumber])

When I open the report, I get a prompt for [txtSomeNumber] and whatever I fill
in gets summed for each row in the detail. So ten rows and I respond 1 gives
me 10 displayed in txtc. txtA shows the correct sum and txtB shows the last
value displayed in the detail section.

So for me, summing a control does not work. Summing a field does work.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
So for me, summing a control does not work. Summing a field does
work.
Click to expand...

As someone else pointed out, you have to set the running sum
property, so my suggestion was incorrect as it stood, but you can
still do it.
 
Back
Top