Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with conditional sums in Access Reports 2

Status
Not open for further replies.

rtoguchi

Technical User
Jan 13, 2004
9
US
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

 
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
 
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"?
 
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
 
Try something like this as the control box def:

=Sum(IIf(StyleA="Y",[qty},0))
 
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.
 
It would look like this
=Sum(IIf(Title="Mr" or Title="Mrs" or Title ="Ms",[your field name],0))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top