Conditional Sums in Reports

  • Thread starter Thread starter Christina
  • Start date Start date
C

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
 
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.
 
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.
 
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.
 
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
Click to expand...


.
Click to expand...
 
Back
Top