Calculations in reports

[bvanscoy678]

I am building a report based of a query. It is a parameter query that searches for results between dates. It pulls up 5 different fields (besides the date) from the same table.



I want to be able to group the dates by month. I don't care about it showing every individul date. [I tried the group on with group and sorting, but I was not successful (I added headers and footers)].

Some fields I need to sum numbers which I have done successfuly by adding =SUM () in the footer.

But, not sure what operator to use for the other fields. They are text fields. I just want to sum how many times a certain field show up. is...if I have 5 choices of colors. then I want it to show Red's total is 10, Blue's totals are 8, ect...

Thanks for any help in sorting this out. I am new, but learning quickly.

[pbaldy]

One way:

=Sum(IIf(FieldName = "Red", 1, 0))

I'd also consider using a totals query and making it the source of a subreport. It would be more dynamic if the possible choices changed.

[bvanscoy678]

I am open to suggestions about a sub report.

I am attempting to make this interactive and easy. I just want a guy to put in the dates using a parameter query, then have the report do everything else.

Do you have a link I can read up on your suggestion?

thank you

[azal]

Hope this helps..

[mduplantis]

yes, that is what I wanted to do.

Thank you very much.