Counting records in a report that meet a criteria

[EdFleeson]

I am putting together a report that has the columns: Customer account, week 1 sales, week 2 sales, etc. The customers are grouped based on State. Each customer does not have sales each week (some weeks are 0). In the Group footer I want to be able to count the number of customers in each group, for each week that had sales over 0. Example Week 1 has 3, week 2 has 5, etc. How and is there a way I can do this?

 

[coco86]

Try this:

Add a field to your report query similar to

WeekHasSales:IIf([Sales]>0,1,0)

Then, in the section footer for your customer, add a field whose controlsource is:

=Sum([WeekHasSales])

 

[EdFleeson]

This will work. Unfort. I have 12 column weeks so I will have to put in 12 different fields in the query. Was hoping to have a formula in the report itself that could look at each row under each column and determine the number. Thanks for the suggestion.

 

[PaulBricker]

You can try one of these as the control source for your textbox.

=Sum(IIf([Week1Sales] > 0,1,0))
=Sum(IIf(Not IsNull([Week1Sales]),1,0))

It depends on how the data is set up as to which will work. Basically summing 1's and 0's is the same as counting. If I'm way off the mark with what you need post back.

Paul

 

[EdFleeson]

Thanks Paul

That did the trick. I knew there had to be away to use a If statement but I was getting caught up in using the Count function and never thought about a sum function.

Thanks again.
ed