Help with conditional sums in Access Reports 2

[rtoguchi]

Could anyone please help with the above mentioned problem? I am looking for the right expression to create the Sum totals.

TableA TableB TableC
StyleA 5 Y
StyleA 5 Y
StyleA 5 N

Sum:"StyleA=Y"10 Sum:"StyleA=N" 5

 

[GoDawgs]

Summarize by the "Y"/"N" field in the report...then in the Footer for that field put a ontrol to calculate the sum. Is that what you're looking for?

Kevin

 

[rtoguchi]

The problem with that is I can't summarize by the "Y/N" because they are text and not numeric fields. By doing summary in the reports, I get a sum for all pieces of "StyleA." I am looking to create a Sum for all pieces of "StyleA with Y" and a Sum for all pieces of "StyleA with N." I can do it in Excel, but haven't been able to translate that into Access.

In the Design view, I get a "=Sum[qty]". Is it possible to change it to something along the lines of "=Sum[qty] if TableC=Y"?

 

[GoDawgs]

For the sorting and grouping of the report, sort by Style first, then by the "Y/N" field. Basically laid out, the report should look like this:

StyleA
Y
Sum
N
Sum

You'd put the Sum expressions in the Footer for the "Y/N" field...so that would be the sum of the quantity where "Y" and "StyleA". Hope that helps.

Kevin

 

[tledwards]

Try something like this as the control box def:

=Sum(IIf(StyleA="Y",[qty},0))

 

[rtoguchi]

Thank you both, it solved my problem.

 

[jh3016]

I am trying to do something similar. I need to get a total on the group "Region".

If Title=Mr or if title = Mrs or if title = Ms, then add up FTE.

Can someone help me? Thanks in advance.

 

[tledwards]

It would look like this
=Sum(IIf(Title="Mr" or Title="Mrs" or Title ="Ms",[your field name],0))