Conditional Sums in Reports

[Christina]

I've been playing around and trying to find a formula
that would work, but I have not been successful. What I
am trying to do is to sum 1 field based on another field
being a certain value:
I have 1 field that is the # of Attendees in a Training
Session
I have another field that is the training session topic
I would like to add all of the attendees that were
trained under a certain topic

I've played around w/ some Sum, Abs, and DSum formulas
but haven't found one that works. Any help you can give
me would be greatly appreciated!
Thanks
Christina

 

[Melissa]

Maybe I am not understanding, but it sounds like you want
to do a Group and Count? Group by "Topic" and Count
attendees. You would do this in a query and then pull onto
a report. Hope this helps.

 

[Christina]

Grouping them doesn't really change anything. I can't
sum or count the attendees that way. The only way
another query would work would be to make a query where I
specify to only query 1 topic at a time and then sum
that. But I'd rather not have to do that because I have
about 14 topics and each time I wanted to know this I
would have to go in and change the query requirements.

 

[Duane Hookom]

A conditional sum can be set up fairly easily. For instance, if you want to
sum all sales for Fridays:
=Sum( Abs(Weekday([SaleDate])=6) * [SalesAmt])
To sum all salaries for females:
=Sum( Abs([Gender]="f") * [Salary])
To count the number of females making more than 50000
=Sum( Abs([Gender]="f" AND [Salary]>50000))

Hopefully you can apply this to your tables and fields.

 

[Guest]

Thanks, that worked great...it didn't seem as if it would
be real difficult...I just couldn't get the formula quite
right. Thanks for all your help.
Christina

-----Original Message-----
A conditional sum can be set up fairly easily. For instance, if you want to
sum all sales for Fridays:
=Sum( Abs(Weekday([SaleDate])=6) * [SalesAmt])
To sum all salaries for females:
=Sum( Abs([Gender]="f") * [Salary])
To count the number of females making more than 50000
=Sum( Abs([Gender]="f" AND [Salary]>50000))

Hopefully you can apply this to your tables and fields.

--
Duane Hookom
MS Access MVP
--

I've been playing around and trying to find a formula
that would work, but I have not been successful. What I
am trying to do is to sum 1 field based on another field
being a certain value:
I have 1 field that is the # of Attendees in a Training
Session
I have another field that is the training session topic
I would like to add all of the attendees that were
trained under a certain topic

I've played around w/ some Sum, Abs, and DSum formulas
but haven't found one that works. Any help you can give
me would be greatly appreciated!
Thanks
Christina

.