Design reports with formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a report that totals three different types of space in two
different buildings. I was able to create subtotals for each type of space
in each building, and a grand total of each different type of space in both
buildings. Now I want to add a "Grand Total", which would be a number adding
up the three different types of space in both buildings. I can't get the
formula right.

I created a text box at the bottom of the report (design view) in the report
footer section, but can only get the formula to show on the report in the
preview mode. I tried this: =sum([OfficeSF]+[StorageSF]+[BackOfficeSF]),
which I hoped would give me the grand total. All that shows is the written
formula. Thanks for any help.
 
If ([OfficeSF]+[StorageSF]+[BackOfficeSF] are calculated fields, it won't
work.

You need to repeat the calculation, ie. if [OfficeSF] is
[OfficeS]*[OfficeF], you need to specify this in the report footer.
Also, is the data grouped. If so you may need to do running sum totals in
the group footer.

John
 
Thanks, John. Then...the formulas I've used so far are:

the sum of my office square footage (OfficeSF) is =sum([officeSF]),
the sum of my storage square footage (StorageSF) is =sum([StorageSF]) and
The sum of my Back Office square footage (BackOfficeSF) is
=sum([BackOfficeSF]).

Then to add those three sums up, I was inputting the formula:

=sum([OfficeSF])+sum([StorageSF])+sum([BackOfficeSF])

I'm guessing that there's something wrong with my syntax since it's not
working. Any idea what's wrong? Thanks!


John said:
If ([OfficeSF]+[StorageSF]+[BackOfficeSF] are calculated fields, it won't
work.

You need to repeat the calculation, ie. if [OfficeSF] is
[OfficeS]*[OfficeF], you need to specify this in the report footer.
Also, is the data grouped. If so you may need to do running sum totals in
the group footer.

John

kleivakat said:
I created a report that totals three different types of space in two
different buildings. I was able to create subtotals for each type of space
in each building, and a grand total of each different type of space in both
buildings. Now I want to add a "Grand Total", which would be a number adding
up the three different types of space in both buildings. I can't get the
formula right.

I created a text box at the bottom of the report (design view) in the report
footer section, but can only get the formula to show on the report in the
preview mode. I tried this: =sum([OfficeSF]+[StorageSF]+[BackOfficeSF]),
which I hoped would give me the grand total. All that shows is the written
formula. Thanks for any help.
Click to expand...
Click to expand...
 
You don't need to use "=Sum", as they're already summed.

control source for your textbox

=[officeSF]+[StorageSF]+[BackOfficeSF]

John


kleivakat said:
Thanks, John. Then...the formulas I've used so far are:

the sum of my office square footage (OfficeSF) is =sum([officeSF]),
the sum of my storage square footage (StorageSF) is =sum([StorageSF]) and
The sum of my Back Office square footage (BackOfficeSF) is
=sum([BackOfficeSF]).

Then to add those three sums up, I was inputting the formula:

=sum([OfficeSF])+sum([StorageSF])+sum([BackOfficeSF])

I'm guessing that there's something wrong with my syntax since it's not
working. Any idea what's wrong? Thanks!


John said:
If ([OfficeSF]+[StorageSF]+[BackOfficeSF] are calculated fields, it won't
work.

You need to repeat the calculation, ie. if [OfficeSF] is
[OfficeS]*[OfficeF], you need to specify this in the report footer.
Also, is the data grouped. If so you may need to do running sum totals in
the group footer.

John

kleivakat said:
I created a report that totals three different types of space in two
different buildings. I was able to create subtotals for each type of space
in each building, and a grand total of each different type of space in both
buildings. Now I want to add a "Grand Total", which would be a number adding
up the three different types of space in both buildings. I can't get the
formula right.

I created a text box at the bottom of the report (design view) in the report
footer section, but can only get the formula to show on the report in the
preview mode. I tried this: =sum([OfficeSF]+[StorageSF]+[BackOfficeSF]),
which I hoped would give me the grand total. All that shows is the written
formula. Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
John, I tried that. Maybe I have it in the wrong section. In my report
footer, I have the three subtotals of different kinds of space. I added a
text box to the report footer for the "total sum", which just adds up the
three numbers that are already shown in the "columns" of the report. In my
report, it just shows the formula, and not the results. I do appreciate your
help!


John said:
You don't need to use "=Sum", as they're already summed.

control source for your textbox

=[officeSF]+[StorageSF]+[BackOfficeSF]

John


kleivakat said:
Thanks, John. Then...the formulas I've used so far are:

the sum of my office square footage (OfficeSF) is =sum([officeSF]),
the sum of my storage square footage (StorageSF) is =sum([StorageSF]) and
The sum of my Back Office square footage (BackOfficeSF) is
=sum([BackOfficeSF]).

Then to add those three sums up, I was inputting the formula:

=sum([OfficeSF])+sum([StorageSF])+sum([BackOfficeSF])

I'm guessing that there's something wrong with my syntax since it's not
working. Any idea what's wrong? Thanks!


John said:
If ([OfficeSF]+[StorageSF]+[BackOfficeSF] are calculated fields, it won't
work.

You need to repeat the calculation, ie. if [OfficeSF] is
[OfficeS]*[OfficeF], you need to specify this in the report footer.
Also, is the data grouped. If so you may need to do running sum totals in
the group footer.

John

I created a report that totals three different types of space in two
different buildings. I was able to create subtotals for each type of
space
in each building, and a grand total of each different type of space in
both
buildings. Now I want to add a "Grand Total", which would be a number
adding
up the three different types of space in both buildings. I can't get the
formula right.

I created a text box at the bottom of the report (design view) in the
report
footer section, but can only get the formula to show on the report in the
preview mode. I tried this: =sum([OfficeSF]+[StorageSF]+[BackOfficeSF]),
which I hoped would give me the grand total. All that shows is the
written
formula. Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Sounds like you have a label rather than a text box control.

--
Duane Hookom
MS Access MVP


kleivakat said:
John, I tried that. Maybe I have it in the wrong section. In my report
footer, I have the three subtotals of different kinds of space. I added a
text box to the report footer for the "total sum", which just adds up the
three numbers that are already shown in the "columns" of the report. In my
report, it just shows the formula, and not the results. I do appreciate your
help!


John said:
You don't need to use "=Sum", as they're already summed.

control source for your textbox

=[officeSF]+[StorageSF]+[BackOfficeSF]

John


kleivakat said:
Thanks, John. Then...the formulas I've used so far are:

the sum of my office square footage (OfficeSF) is =sum([officeSF]),
the sum of my storage square footage (StorageSF) is =sum([StorageSF]) and
The sum of my Back Office square footage (BackOfficeSF) is
=sum([BackOfficeSF]).

Then to add those three sums up, I was inputting the formula:

=sum([OfficeSF])+sum([StorageSF])+sum([BackOfficeSF])

I'm guessing that there's something wrong with my syntax since it's not
working. Any idea what's wrong? Thanks!


:


If ([OfficeSF]+[StorageSF]+[BackOfficeSF] are calculated fields, it won't
work.

You need to repeat the calculation, ie. if [OfficeSF] is
[OfficeS]*[OfficeF], you need to specify this in the report footer.
Also, is the data grouped. If so you may need to do running sum
Click to expand...
totals
in
the group footer.

John

I created a report that totals three different types of space in two
different buildings. I was able to create subtotals for each type of
space
in each building, and a grand total of each different type of space in
both
buildings. Now I want to add a "Grand Total", which would be a number
adding
up the three different types of space in both buildings. I can't
Click to expand...
get
the
formula right.

I created a text box at the bottom of the report (design view) in the
report
footer section, but can only get the formula to show on the report
Click to expand...
in
the
preview mode. I tried this: =sum([OfficeSF]+[StorageSF]+[BackOfficeSF]),
which I hoped would give me the grand total. All that shows is the
written
formula. Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top