COUNT of values from multiple fields

  • Thread starter Thread starter CSDunn
  • Start date Start date
C

CSDunn

Hello,
I have a situation in which each record on a report has 40 fields, any one
of which can contain a value of 'C', or 'I', or 'B'. I need to create one
calculated field on the report that will COUNT the number of fields with a
value of 'C'(and shows the integer result), and another calculation that
will take the COUNT of the fields with a value of 'C' and divide that by the
total number of fields. The second calculation would be expressed as a
percentage, allowing for two decimal places.

The field names for the forty fields are Q1, Q2, Q3.....Q40. A sample of the
records would look like this:

StudentID Name Q1 Q2 Q3 Q4 #Correct %Correct
1234 John C C I B 2
50.00%

How would I accomplish this, and what event would the procedures be assigned
to in the report? The records in the report that requires the calculations
is in the detail section of the report, and the report itself is a sub
report located within the detail section of the main report.

Thanks for your help!

CSDunn
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just make the #Correct TextBox a calculated field on the report.
Something like this in the #Correct TextBox's Control Source property:

=IIf(Q1="C",1,0) + IIf(Q2="C",1,0) + IIf(Q3="C",1,0) + IIf(Q4="C",1,0)

This assumes the names of the TextBoxes that contain the Quarterly
data are Q1, Q2, Q3 & Q4. If not, change the calculation's names to
the correct names.

The %Correct calculation would be:

=[#Correct]/4.0

Use 4.0 to get a floating point result.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQD+vxYechKqOuFEgEQLVMgCglJj8Bm+Ibg1X9KrIyE16V1v3OF8An2Kc
4mfzoQ73KmuK1z7zOPLo9Aoy
=sght
-----END PGP SIGNATURE-----
 
Back
Top