conditional counting in reports 1

[leeroi]

I know just enough about Access to run basicCount and Sum functions in report footers. What I need to do is to count the number of occurences in a column, IF the data meets a certain criteria. For example, if the field name is "Wines", and there are various entries in the column, such as "Chablis" and "Merlot", how do I count the number of just "Chablis" or just "Merlot"?. Please help...

Lee

 

[Remou]

Perhaps:
[tt]Sum(IIf(txtWine="Chablis",1,0))[/tt]
But if you have a lot of different items to sum, a subreport in the report footer may be better.

 

[leeroi]

Wouldn't I use "count" rather than "sum"? And since the "wines" is a named field, would I include "txt" as a prefix, or should it be enclosed in brackets [Wines]

Lee

 

[Remou]

You would not use Count because that would count the zeros, whereas Sum adds only the ones. If Wines is the name of your field, that is indeed what you should use.

 

[leeroi]

Yes, that does work. Thank you very much!

 

[Melagan]

Remou gets a star for this one. Before, I was doing the IIF part in my query then summing on the report. The IIF in the report makes a lot more sense; right on!


~Melagan
______
"It's never too late to become what you might have been.