Counting fields in a query

[Guest]

Hello

I am trying to create a formula in a text box, placed in the group footer of
report. to count specific text. For example, I have a field for race in
which there are several selections (White, Black, Hispanic...). When I setup
the table I made this field (Race) a combo list.

How do I create a formula that would count the number for each category...

Thanks

 

[Guest]

Sum the result of an expression which returns 1 or 0 depending on the value,
e.g. to count all whites :

=Sum(IIF([Race] = "White",1.0))

The alternative would be to include an unlinked subreport in the footer
which returns each value and its count by basing the subreport on a query
such as:

SELECT Race, COUNT(*) As CountofRace
FROM YourTable
GROUP BY Race;

This way you don't need to know what values of Race are present in the table
in advance. As this is to be in a group footer, however you'll need to
restrict the query to the current group, e.g. if the grouping is by State:

SELECT Race, COUNT(*) As CountofRace
FROM YourTable
WHERE State = Reports!YourReport!State
GROUP BY Race;

If the main report is restricted in any other way you'd need to include that
in the query's WHERE clause too.

 

[Guest]

very helpful post!

Sum the result of an expression which returns 1 or 0 depending on the value,
e.g. to count all whites :

=Sum(IIF([Race] = "White",1.0))

The alternative would be to include an unlinked subreport in the footer
which returns each value and its count by basing the subreport on a query
such as:

SELECT Race, COUNT(*) As CountofRace
FROM YourTable
GROUP BY Race;

This way you don't need to know what values of Race are present in the table
in advance. As this is to be in a group footer, however you'll need to
restrict the query to the current group, e.g. if the grouping is by State:

SELECT Race, COUNT(*) As CountofRace
FROM YourTable
WHERE State = Reports!YourReport!State
GROUP BY Race;

If the main report is restricted in any other way you'd need to include that
in the query's WHERE clause too.

Hello

I am trying to create a formula in a text box, placed in the group footer of
report. to count specific text. For example, I have a field for race in
which there are several selections (White, Black, Hispanic...). When I setup
the table I made this field (Race) a combo list.

How do I create a formula that would count the number for each category...

Thanks

 

[Guest]

Thankyou, Diana.

BTW, regarding the use of the IIf function to do conditional aggregation:

=Sum(IIF([Race] = "White",1.0))

you might see one of the following approaches recommended sometimes:

=Sum(Abs([Race] = "White))
=Sum(([Race] = "White)*-1)

They work, but don't do it. They rely on the implementation of Boolean
values as 0 or -1. Reliance on the implementation is bad programming
practice.