Best way to count the number of records that have a specific field value?

[Ceichhorn]

So I'm trying to do a calculation in the footer of a grouping, and in that calculation I need to know how many records had a specific value for a specific field, let's call it lngPropertyID. What is the best way to do this sort of calculation? For example, let's say this field could have a value of 1 or 2, and I wanted to calculate what percentage of the records in this group had lngPropertyID=1? Thanks in advance!

[andy49]

any use?

https://support.office.com/en-gb/art...2-f835998bd72e

[ranman256]

in a query you can do the total counts. qsTotal
and finally in your data query ,join in the qsTotal into your data query.

in the report footer put
a field with the Total count All items, txtAllTotal
and a field =Count(field) with the count of the 1 dept, txtDeptTot
and finally a field to divide the two, =txtDeptTot/txtAllTotal
set to pct.

[Ceichhorn]

Thanks andy! This allows me to count ALL records in a group, but it doesn't seem to provide a method to count all records that meet a specific requirement, e.g. where lngPropertyID=1. I tried putting that in to the count function as an argument and I got back the count of all records in the group, not just those that had lngPropertyID=1.

[Ceichhorn]

in a query you can do the total counts. qsTotal
and finally in your data query ,join in the qsTotal into your data query.

in the report footer put
a field with the Total count All items, txtAllTotal
and a field =Count(field) with the count of the 1 dept, txtDeptTot
and finally a field to divide the two, =txtDeptTot/txtAllTotal
set to pct.

Ranman, thanks for the suggestion! When I do the count of the field, how do I get the count to recognize that I only want it to count records that contain a specific field value? If I put in =Count(txtDptTot="Example"), I get back a count of all records in the group, not just those that meet the criteria I'm providing.

[ssanfu]

Try this...(The quick and dirty method.)
In the footer, add two text boxes.
Set the control source for the 1st text box to "= Sum(IIF(lngPropertyID = 1,1,0))"
In the 2nd text box, set the control source to "= Sum(IIF(lngPropertyID = 2,1,0))"
Obviously, do not include the quotes......

[Ceichhorn]

Try this...(The quick and dirty method.)
In the footer, add two text boxes.
Set the control source for the 1st text box to "= Sum(IIF(lngPropertyID = 1,1,0))"
In the 2nd text box, set the control source to "= Sum(IIF(lngPropertyID = 2,1,0))"
Obviously, do not include the quotes......

This did it-thanks!