Calculate percentage

  • Thread starter Thread starter CarlaInJax
  • Start date Start date
C

CarlaInJax

I have a report that counts the number of surveys sent, the number of surveys
returned, the number of responses based upon satisfaction (1=very satisfied,
2=satisfied, 3=neutral, etc.). This report also calculates the percentages
of each UNTIL I throw in a date range. When I run the report for all dates,
everything is perfect. When I change the query to capture only the data
within a given date range, my percentages come out crazy. I do not know code
and am using the following to calculate the percentage of very satisfied
responses: =DCount("*","calls","[survey returned]='1'")/Count([Survey
Returned]). Like I said, this works well when capturing all dates, but I'm
obviously missing something to get it to calculate percentages for given date
ranges.
Thanks for your help.
 
You did not post your query SQL so no help there but if you use a select
query ahead of what you have now you should be able to limit the records to
the date range you wish.
 
The DCount considers all the records of the table, no connection at all with
the report, well, taking into account ITS own criteria supplied in its third
argument, while Count, inside the report, considers only the records already
filtered by the report, and groups, at the point (section of the report) you
use it.


Probably an example will just make things simpler to understand.


Example.

Open Northwind.

Make a new report, on Products, with the Wizard, with CategoryID and
ProductName. Have a GROUP on CategoryID.

In design view,

add a textbox control, in the "Report Header" section, and have its
control source

= Count("*")

open the Group properties sheet open if not already open
change the Group Footer property to Yes (for group CategoryID)
in the CategoryID Footer section, add a textbox control and have its
control source

= Count("*")


View the report in preview.

Observe:

the Count, in the report header, gives the total number of records, in
the report.
the Count, in the group footer section, gives the total number for that
category, in the report

You can open the report with filters, such as ProductName <= "K", and the
counts take into account the extra added filter.


So, to have the percentage, it is a matter to have a new control =
ControlNameInTheGroupFooter / ControlNameInTheReportHeader

(sure, you can have the first two text controls we just added turned
invisible, once you are convinced that 'they work' as described).


Note that Count( ) does NOT exist outside the report! (well, there is one
in SQL, but that is another story) while DCount( ) is a VBA function
available almost anywhere a VBA function can be used!



Vanderghast, Access MVP
 
Back
Top