Query to count total number of occurences in a range

  • Thread starter Thread starter Ron Burgundy
  • Start date Start date
R

Ron Burgundy

I am trying to design a query (called redline count) based on information
from another query (called information). The information query contains 125
records (or rows). One column is set up to count the number of days from
start to finish using a datediff formula.

I want the redline count query to calculate how many records finished in
less than 21 days; between 21 and 59 days; 60 or more days.

Thanks for any help,
 
I am trying to design a query (called redline count) based on information
from another query (called information). The information query contains 125
records (or rows). One column is set up to count the number of days from
start to finish using a datediff formula.

I want the redline count query to calculate how many records finished in
less than 21 days; between 21 and 59 days; 60 or more days.

Thanks for any help,
Click to expand...

the easiest way is to create an expression to create the bins.

IIF(DaysElapsed<21,"A",IIF(DaysElapsed>=21 And
DaysElapsed<=59,"B","C"))

then you can group on that expression.
 
SELECT Count(IIF([CalcDays]<21,1,Null) as Under21
, Count(IIF([CalcDays]>20 and [CalcDays]<60,1,Null) as 21To59
, Count(IIF([Calcdays]>60,1,Null) as Over60
FROM [Information] as RC

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks for such a quick response, piet. Will this still work if the field is
defined as a number field and not a date field? The formula I'm using only
calculates for entries that have started and not finished, it leaves the rest
blank.

I hope this makes sense
 
Thanks John,

I have seen quite a few difficult questions that you have the answers for so
I appreciate your help. I am brand new to the online discussions so I could
use a little clarification on how to enter this formula into my query.

Regards,

Ron
 
I think the way you set it up would work, but I will probably need to make
some adjustments. Let me give a little more detail:

Information Query:

1. Contains a field for "started" or "not started" or "finished"
2. If "started" is selected for this field and a date is entered into the
field for start date then the formula starts counting the days from that date.
3. The field with the formula returns a simple number

So, how do I use the method you described (creating bins and counting within
them) for regular numbers and not days?

Thanks again
 
Thanks John,

I have seen quite a few difficult questions that you have the answers for so
I appreciate your help. I am brand new to the online discussions so I could
use a little clarification on how to enter this formula into my query.

Regards,

Ron


John Spencer said:
SELECT Count(IIF([CalcDays]<21,1,Null) as Under21
, Count(IIF([CalcDays]>20 and [CalcDays]<60,1,Null) as 21To59
, Count(IIF([Calcdays]>60,1,Null) as Over60
FROM [Information] as RC

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Ron said:
I am trying to design a query (called redline count) based on information
from another query (called information). The information query contains 125
records (or rows). One column is set up to count the number of days from
start to finish using a datediff formula.

I want the redline count query to calculate how many records finished in
less than 21 days; between 21 and 59 days; 60 or more days.

Thanks for any help,
Click to expand...
Click to expand...
 
That is a query. It is not a formula.

If you are trying to do something like this in a query you will need to use
three subqueries as calculated fields. Assuming the field in the Information
query is entitled calcDays, you would need something in one of the field
"boxes" that looked like the following:

Field: Under21: (Select Count(*) FROM [Information] Where [CalcDays] <21)

Or you could use the VBA Dcount function

Field: Under21: DCount("*","Information","[CalcDays] < 21")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ron said:
Thanks John,

I have seen quite a few difficult questions that you have the answers for so
I appreciate your help. I am brand new to the online discussions so I could
use a little clarification on how to enter this formula into my query.

Regards,

Ron


John Spencer said:
SELECT Count(IIF([CalcDays]<21,1,Null) as Under21
, Count(IIF([CalcDays]>20 and [CalcDays]<60,1,Null) as 21To59
, Count(IIF([Calcdays]>60,1,Null) as Over60
FROM [Information] as RC

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Ron said:
I am trying to design a query (called redline count) based on information
from another query (called information). The information query contains 125
records (or rows). One column is set up to count the number of days from
start to finish using a datediff formula.

I want the redline count query to calculate how many records finished in
less than 21 days; between 21 and 59 days; 60 or more days.

Thanks for any help,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top