Greetings, I have designed a Wor

[OCM]

Greetings,

I have designed a Workers Productivity report based on a crosstab query to track how many applications the workers processed within 10 business days(<=10) and how many over 10 business days (>10) for the Fiscal Year.
1. How do I get a total for each Workers each Month? For example, if Alisa processed 4
applications within (<= 10) days and & 5 applications over(>10)days,
how do I get the report to show Total for Aldridge = 9.
2. How do I get to show both <= 10 and > 10 for each CaseWorkers, even if there is no data for them?

Thanks,
DD

 

[dhookom]

I am confused why you wanted both days and then &quot;Each Month&quot;. You crosstab should group by worker as a row heading. If you have a table of all workers in your query, double-click the join line and select the option that displays all the records from your workers table.

If this doesn't help then post your sql view.

Duane
MS Access MVP

 

[OCM]

What I tried to get in the report was to first find out how many applications each worker processed within 10 days (<=10) and also over 10 days (>10) and then be able to add both for that worker.
I don't have a separate table for Workers, so i didn't have to use join...
I tyied to attach an excel sample to show how the report should look like, but wasn't successful. The following is the sql view:

TRANSFORM Count(*) AS N
SELECT tblApplicants.BHCAWorker, IIf([EligDays]<=10,&quot;<=10&quot;,&quot;>10&quot AS ED, Count(*) AS T
FROM tblApplicants
WHERE (((tblApplicants.AppStatus)=&quot;NEW&quot AND ((tblApplicants.EligStatus)=&quot;ELIG&quot AND ((tblApplicants.AppRec) Is Not Null And (tblApplicants.AppRec) Between [Forms]![frmFiscal Year Dialog]![BeginningDate] And [Forms]![frmFiscal Year Dialog]![EndingDate]))
GROUP BY tblApplicants.BHCAWorker, IIf([EligDays]<=10,&quot;<=10&quot;,&quot;>10&quot
PIVOT Month([AppRec]) In (7,8,9,10,11,12,1,2,3,4,5,6);


If this is not still clear, please send me e-mail so i can attach the sample excel report and the report i designed in access.

Thanks,

DD

 

[jiqjaq]

You need to use GroupFooters for the Grouping on worker to calculate the totals. But you need to Group first on worker, then on the processing period. Seems you've done the reverse.

How are you tracking those periods of business days that exist in both months? Most months only have about 22 business days. If someone starts processing an application after the 18th of the month, there's at most 9 business days left in that month. If they finish in 10 days, that last day could be in the next calendar month. Will you count that as < 10 in the finishing month, since only 1 day was in the reporting month? Or will it be = 10 for the elapsed period? OR not counted at all since the period wasn't entirely in the given month?

 

[OCM]

I checked my grouping & I did grouping with worker first and then processing period.

The way I track the business days is as follows:
I have a field called EligDays, which is a result of calculations. If for example, EligDays = 11, then the worker, let's say Rose processed the application over 10 business days (>10) and if EligDays = 9, then Rose processed it within 10 business days (<=10) etc. and I like to be able to get a Total for Rose (how many >10 she processed + how many <=10).

If there is a way to e-mail you the sample query and report, please let me know. It might be more helpful!

Thanks,

DD