Using Record Count in an equation

  • Thread starter Thread starter Reici
  • Start date Start date
R

Reici

I have a "yes/no" field called Sold. I need to track and report what percent
of total solicitations sold. I thought I could use the built in sum feature
which provides a record "Count(*)" for each record in a group. However, I
dont know how to set up the equation as the control is called the same for
each grouping.

How do I either use that control or set up a calculated field to count
total records and count the number of records with Sold="yes".
 
In the Report Footer section, place a text box with this in its Control
Source:
= - Sum([Sold])

This works because Access uses -1 for True, and 0 for False. Hence summing
the yes/no field gives the negative count of the number of Yeses.
 
Thanks Allen, but can I press you a bit further? I need to report the % sold
of the total. Would that be = Standard(Sum([Sold]/+ Sum([Sold])) Get the
Idea? I am not a good equation writer, need to study.

Thanks again


Allen Browne said:
In the Report Footer section, place a text box with this in its Control
Source:
= - Sum([Sold])

This works because Access uses -1 for True, and 0 for False. Hence summing
the yes/no field gives the negative count of the number of Yeses.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Reici said:
I have a "yes/no" field called Sold. I need to track and report what
percent
of total solicitations sold. I thought I could use the built in sum
feature
which provides a record "Count(*)" for each record in a group. However, I
dont know how to set up the equation as the control is called the same for
each grouping.

How do I either use that control or set up a calculated field to count
total records and count the number of records with Sold="yes".
Click to expand...
Click to expand...
 
Total count is:
=Count("*")

You can therefore use:
= - Sum([Sold]) / Count("*")
assuming that the count is not zero.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Reici said:
Thanks Allen, but can I press you a bit further? I need to report the %
sold
of the total. Would that be = Standard(Sum([Sold]/+ Sum([Sold])) Get
the
Idea? I am not a good equation writer, need to study.

Thanks again


Allen Browne said:
In the Report Footer section, place a text box with this in its Control
Source:
= - Sum([Sold])

This works because Access uses -1 for True, and 0 for False. Hence
summing
the yes/no field gives the negative count of the number of Yeses.

Reici said:
I have a "yes/no" field called Sold. I need to track and report what
percent
of total solicitations sold. I thought I could use the built in sum
feature
which provides a record "Count(*)" for each record in a group.
However, I
dont know how to set up the equation as the control is called the same
for
each grouping.

How do I either use that control or set up a calculated field to count
total records and count the number of records with Sold="yes".
Click to expand...
Click to expand...
Click to expand...
 
Back
Top