Reports by days open 1

[kastaman]

Hi there,

I have a report that determines the # of days a call has been open. I've been able to determine the # of calls by operator that are open, what I'm having a bit of problem is seperating and counting the calls by 5-9 days open, 10-19, 20-29.........
Should I start in the query or the report itself?
Please advise. I'm fairly new to Access.

Thanks in advance,

Kastaman

 

[tomschafer]

I would add something like this to your query...

select
sum(iif([Days Open] between 5 and 9, 1, 0)) as "5 - 9",
sum(iif([Days Open] between 10 and 19, 1, 0)) as "10 - 19",
sum(iif([Days Open] between 20 and 29, 1, 0)) as "20 - 29"
from
.................

This uses the imbedded if function-iif. The syntax is iif(condition, true, false). So we are evaluating the days open column if it is between the stated range we return a 1 otherwise a 0. Then we sum all those 1's and 0's up. Hope it makes sense. Good Luck!

 

[kastaman]

Thanks Pezamystik - I'm not too sure how to apply your response but I'll try my best.

Where do I apply this sum? Would I place it in the criteria fields? Do I need to create a column for each days open range?

Thanks for you input.

Kastaman.

 

[tomschafer]

Copy and paste each of these as new fields in your query if you are used to building a query in design view. Then you can add each of them to your report- you will have 3 new fields: 5 - 9, 10 - 19 and 20 - 29.

sum(iif([Days Open] between 5 and 9, 1, 0)) as "5 - 9"
sum(iif([Days Open] between 10 and 19, 1, 0)) as "10 - 19"
sum(iif([Days Open] between 20 and 29, 1, 0)) as "20 - 29"

 

[kastaman]

Pezamystik - I'm getting a mismatch data type error. any thoughts?

Kastaman

 

[tomschafer]

Is the [Days Open] field numeric??