report statistics

N

nevpoe

I am trying to report some percentages on my report and am unsure how to set
it up. I need to report what percentage was between a specific range, what
was below that range, and what was above that range. I tried a query but you
cant run these all at once. Thanks for the help.
 
J

John Nurick

You can do it all at once in a query, though it's beyond what can
conveniently be done with the query designer. Here's an example:

SELECT
SUM(IIF([XXX]<400,1,0))*100/Count([XXX]) AS PercentUnder400,
SUM(IIF([XXX]>=400 AND [XXX]<500,1,0))*100/Count([XXX]) AS Percent
400To500,
SUM(IIF([XXX]>=500 ,1,0))*100/Count([XXX]) AS PercentOver500
FROM tblMain;

If you want to display the percentages in textboxes in the report footer
(a typical way of doing it) you could instead use expressions like this:

=DCount("[XXX]","tblMain","[XXX]>=400 AND [XXX]<500") * 1000 /
DCount("*","tblMain")
 
N

nevpoe

Thanks, I will give it a try.

John Nurick said:
You can do it all at once in a query, though it's beyond what can
conveniently be done with the query designer. Here's an example:

SELECT
SUM(IIF([XXX]<400,1,0))*100/Count([XXX]) AS PercentUnder400,
SUM(IIF([XXX]>=400 AND [XXX]<500,1,0))*100/Count([XXX]) AS Percent
400To500,
SUM(IIF([XXX]>=500 ,1,0))*100/Count([XXX]) AS PercentOver500
FROM tblMain;

If you want to display the percentages in textboxes in the report footer
(a typical way of doing it) you could instead use expressions like this:

=DCount("[XXX]","tblMain","[XXX]>=400 AND [XXX]<500") * 1000 /
DCount("*","tblMain")



I am trying to report some percentages on my report and am unsure how to set
it up. I need to report what percentage was between a specific range, what
was below that range, and what was above that range. I tried a query but you
cant run these all at once. Thanks for the help.
Click to expand...
Click to expand...
 
Top