multiple counts, same calculated field, different parameters

[acmeelec3]

I have a summary report that I need 3 seperate counts for. Report is based on
query of table [maxrep_workorder]. Counts are based on fields [actfinish]
(which is actual finish date) and [wo8] (which is reported date) and
[daysopen] which is a calculated field created within the query
daysopen: (DateDiff("d",[WO8],[ACTFINISH])).

My query looks for [actfinish] between #05/01/09# and #05/31/09#. This
count is in Report header and returns correct count of total workorders
completed during that date range.

I need 2 more counts based on [daysopen]. I need one count for
[daysopen]<11 and a seperate count of [daysopen]>10.

I then need percentages that shows what percent of first count (total
completed) counts 2 and 3 are. eg; 736 total completed, 494 completed on
time (67%), 242 exceed time (33%)

I have not been using code or sql - just the query and expressions to try
and get this to work and it's kicking my butt. Can anyone help me???

 

[John Spencer MVP]

Count(IIF(DaysOpen<11,1,Null))

Count(IIF(DaysOpen>10,1,Null))

Count(IIF(DaysOpen<11,1,Null)/Count(DaysOpen)
Count(IIF(DaysOpen>10,1,Null))/Count(DaysOpen)

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

 

[John W. Vinson]

Count(IIF(DaysOpen<11,1,Null))

Count(IIF(DaysOpen>10,1,Null))

Count(IIF(DaysOpen<11,1,Null)/Count(DaysOpen)
Count(IIF(DaysOpen>10,1,Null))/Count(DaysOpen)

John, don't you mean Sum(IIF(..., 1, Null)) rather than Count? The Count()
operation will return all records.

 

[John Spencer MVP]

Either one works.
Count counts the presence of a value and NULL does not "count" as a value.

I could have used the alternative of

Abs(Sum(DaysOpen<11))
Abs(Sum(DaysOpen>10))

I do like that, but it is a bit hard to understand if you are not versed in
how Access handles True and False.

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

 

[John W. Vinson]

Either one works.
Count counts the presence of a value and NULL does not "count" as a value.

Sorry for jumping in - right you are of course!

 

[John Spencer]

John,
Feel free to jump in anytime. I have learned a lot from you over the
years. Especially when I was first starting to use Access and was often
lost, your postings came to my aid many, many times.

So, even though I've said it before, it bear repeating - Thank you for
sharing your knowledge.

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

 

[James A. Fortune]

John,
Feel free to jump in anytime. I have learned a lot from you over the
years. Especially when I was first starting to use Access and was often
lost, your postings came to my aid many, many times.

So, even though I've said it before, it bear repeating - Thank you for
sharing your knowledge.

Ditto that.

James A. Fortune
(e-mail address removed)