Total Monthly Average box

  • Thread starter Thread starter Tyler at Creme
  • Start date Start date
T

Tyler at Creme

I am using the following formula in a query to give a percentage of column B
as a whole of column A:

ItoT:
Format(Round(Sum([Weekly_Data_Query]![Tours])/Sum([Weekly_Data_Query]![Inquiries]),2),"Percent")

It seems to look okay in a report give 20 different customer rows, but I
want to have an average of all customers at the end of each month on the
report.

What kind of box and formula will give me this, because the format ribbon
only allows me to count values for this calculated query field?
 
I rarely if ever format a value in a query. If you want a number, leave it a
number and provide the formatting in your text box on your report. Queries
columns shouldn't be formatted unless the results are pushed to another
system that requires a formatted value.
 
There's no real need to do it in the query at all. If you group the report
by month you can include a group footer with a text box with a ControlSource
of:

=Sum([Tours])/Sum([Inquiries])

Set the text box control's Format property to Percent. the percent format
automatically rounds to two decimal places.

What this will give you is the sum of all values in the Tours field for each
month as a percentage of the sum of all values in the Inquiries field for the
month.

Ken Sheridan
Stafford, England
 
Back
Top