Age of Work Report

[Guest]

I am trying to create an "Age of Work" report that compares the start date
and the completion date of batches of work, then reports the # of batches
completed in :

0 - 5 days,
6-10 days,
11-15 days,
16-20 days,
21-30 days, and
over 30 days.

This report also needs to calculate what percentage of the total work was
completed in the above timeframes.

I have created the queries to select the count of records for the
individual timeframes but I cannot find a way to pull the data for each
timeframe within one query (calculating the count of records in each
timeframe), and then calculating the percentages in an access report. Any
help would be greatly appreciated

 

[David Lloyd]

The following two queries (the first feeds the second) allow you to count
the totals for each group in one query. To get the total count for the
percentage calculation, you could use a textbox to sum the various group
counts, or you could use the DCount function.

Query 1 (Named DateGrouping)

SELECT TestDate.DateStart, TestDate.DateCompleted,
DateDiff("d",[DateStart],[DateCompleted]) AS Days,
IIf([Days]<=5,"0-5",IIf([Days]<=10,"6-10",IIf([Days]<=15,"11-15",IIf([Days]<=20,"16-20",IIf([Days]<=30,"21-30","Over
30"))))) AS [Group],
IIf([Days]<=5,1,IIf([Days]<=10,2,IIf([Days]<=15,3,IIf([Days]<=20,4,IIf([Days]<=30,5,6)))))
AS SortOrder
FROM TestDate;

Query 2

SELECT DateGrouping.Group, Count(*) AS [Count]
FROM DateGrouping
GROUP BY DateGrouping.Group, DateGrouping.SortOrder
ORDER BY DateGrouping.SortOrder;


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to create an "Age of Work" report that compares the start date
and the completion date of batches of work, then reports the # of batches
completed in :

0 - 5 days,
6-10 days,
11-15 days,
16-20 days,
21-30 days, and
over 30 days.

This report also needs to calculate what percentage of the total work was
completed in the above timeframes.

I have created the queries to select the count of records for the
individual timeframes but I cannot find a way to pull the data for each
timeframe within one query (calculating the count of records in each
timeframe), and then calculating the percentages in an access report. Any
help would be greatly appreciated

 

[Guest]

Thank you very much!