Calculating Percentages

[hotplate]

Suppose I had records that had a starting date and a closing date.

How would I find the percentage of records that closed between 1 and
10 days, 11 - 20 days, 21 - 30 days, and 31 + days?

I think I can do this with about 5 queries, but is there an easier way?

 

[John Spencer MVP]

You should be able to do this all in one query.

SELECT Count(IIF(DateDiff("d",[StartingDate],[Closing Date]<=10,1,Null)) as
1To10Days
, Count(IIF(DateDiff("d",[StartingDate],[closing Date] Between 11 and
20,1,Null)) as 11To20Days
, Count(*) as TotalRecords
, Count(IIF(DateDiff("d",[StartingDate],[closing Date]<=10,1,Null))/Count(*)
as Percent1To10Days
FROM [Some Table]

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

 

[hotplate]

Thank you.

You should be able to do this all in one query.

SELECT Count(IIF(DateDiff("d",[StartingDate],[Closing Date]<=10,1,Null)) as
1To10Days
, Count(IIF(DateDiff("d",[StartingDate],[closing Date] Between 11 and
20,1,Null)) as 11To20Days
, Count(*) as TotalRecords
,  Count(IIF(DateDiff("d",[StartingDate],[closing Date]<=10,1,Null))/Count(*)
as Percent1To10Days
FROM [Some Table]

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

Suppose I had records that had a starting date and a closing date.

How would I find the percentage of records that closed between 1 and
10 days, 11 - 20 days, 21 - 30 days, and 31 + days?

I think I can do this with about 5 queries, but is there an easier way?