Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-03-2018, 01:24 PM   #1
sajarac
Newly Registered User
 
Join Date: Aug 2015
Posts: 107
Thanks: 37
Thanked 10 Times in 10 Posts
sajarac is on a distinguished road
Sum and Count based in a Condition

Hello,

Hope someone can point me in the right direction.

In my report I have several records but only some has a $$ value for the received PO. I would like to 1. Count if the POValue is greater that 1, and 2. sum if the POValue is greater that 1.

Sorry is a silly question but I am stuck

sajarac is offline   Reply With Quote
Old 08-03-2018, 01:52 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,434
Thanks: 40
Thanked 3,367 Times in 3,262 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Sum and Count based in a Condition

you would use a sum for the count as well

in an aggregate query, group by whatever fields are required and for your count

sum(iif(POValue>1,1,0))

and for the sum

sum(iif(POValue>1,POValue,0))
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-03-2018, 02:00 PM   #3
sajarac
Newly Registered User
 
Join Date: Aug 2015
Posts: 107
Thanks: 37
Thanked 10 Times in 10 Posts
sajarac is on a distinguished road
Re: Sum and Count based in a Condition

Hi thank for your prompt reply.

I forgot to tell you one thing:

I would like to sum and count other fields values i.e.

Sum if POValue>1 , Sum Quatity
Count if POValue>1, Count Quantity

is this possible also?

sajarac is offline   Reply With Quote
Old 08-03-2018, 02:14 PM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,434
Thanks: 40
Thanked 3,367 Times in 3,262 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Sum and Count based in a Condition

for the sum, just change the field you want to sum

sum(iif(POValue>1,Quantity,0))

However your count requirement makes no sense
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
sajarac (08-03-2018)
Old 08-03-2018, 02:23 PM   #5
sajarac
Newly Registered User
 
Join Date: Aug 2015
Posts: 107
Thanks: 37
Thanked 10 Times in 10 Posts
sajarac is on a distinguished road
Re: Sum and Count based in a Condition

Hi, sorry probably that is why I am confused.

I have a report, in that report I have 100 records, but only 15 of them have a PO Received.

at the end of my report I have several fields where I am getting the total of every single column. i.e

Sum[Quantity], Count[Orders]

But I would like to include at the end of the report some fields that tells me if the record has a PO received (value greater than 1) count how many orders and give me the sum of those orders.
sajarac is offline   Reply With Quote
Old 08-03-2018, 02:41 PM   #6
sajarac
Newly Registered User
 
Join Date: Aug 2015
Posts: 107
Thanks: 37
Thanked 10 Times in 10 Posts
sajarac is on a distinguished road
Re: Sum and Count based in a Condition

I finally got it, and it was thank you to your advice.

Many many many thanks!!!!

sajarac is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add text to query based on count then reset count leighturner Queries 7 12-06-2010 04:37 AM
Record Count Condition Not working khwaja Modules & VBA 4 09-05-2010 06:03 PM
Count Cells With Condition Owned1Up Queries 2 07-01-2005 12:25 PM
Count records with condition le888 Forms 2 02-09-2004 06:54 AM
Sum based on a condition Jill Reports 2 12-21-2000 05:25 AM




All times are GMT -8. The time now is 10:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World