Calculation from Counts of Yes/No Data

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

Guest

I've created a report designed to show our Salesmen how many quotations
they've converted to orders. It's based on a parameter query, and Count(*)
gives me the total quotations, and the number of records in each group (based
on the Yes/No data in the table) is shown in the group footer. I would like
to calculate their success rate in percentage format but am at a loss. Could
anyone please offer any suggestions? I'm using Access 97.
 
To Count Yes/No fields you can use
Abs(Sum([TheYesNoField]))

To get a percentage
Abs(Sum([TheYesNoField]))/ IIF(Count(*)>0,Count(*),1)

If you are guaranteed to have at least one record then you can simplify that
to
Abs(Sum([TheYesNoField]))/Count(*)
 
Thanks a million John - couldn't have been clearer.

Regards
John Hannah

John Spencer said:
To Count Yes/No fields you can use
Abs(Sum([TheYesNoField]))

To get a percentage
Abs(Sum([TheYesNoField]))/ IIF(Count(*)>0,Count(*),1)

If you are guaranteed to have at least one record then you can simplify that
to
Abs(Sum([TheYesNoField]))/Count(*)


John Hannah said:
I've created a report designed to show our Salesmen how many quotations
they've converted to orders. It's based on a parameter query, and Count(*)
gives me the total quotations, and the number of records in each group
(based
on the Yes/No data in the table) is shown in the group footer. I would
like
to calculate their success rate in percentage format but am at a loss.
Could
anyone please offer any suggestions? I'm using Access 97.
Click to expand...
Click to expand...
 
Back
Top