Based on your feed back, if I have understood correctly, the Data table is something like :
Code:
EmployeeAssigned Loan Step DueDate CompletedDate
A ABC Step1 1/1/2012 1/5/2012
B ABC Step2 1/3/2012 1/5/2012
A ABC Step3 1/4/2012 1/6/2012
C DEF Step1 1/2/2012 1/5/2012
B DEF Step2 1/4/2012 1/5/2012
D DEF Step3 1/5/2012
Just check out if below gives some guidelines :
The Data table :
tblLoans with Fields
EmployeeAssigned
Loan
Step
DueDate
CompletedDate
__________________________________________________ __
Make a table manually containing the Number of Days needed for report (If you need 60, add numbers from 0 - 65)
tblDaysForReport with Field
Num - PK - Type Number (Contains numbers from 0 to 65)
__________________________________________________ __
Design a Make Table Query which generates the Report Dates
This is the 1st Query that has to be run after making the above table
qryGenerateReportDates
Code:
SELECT
tblDaysForReport.Num,
DateAdd("d",[Num],[Enter Start Date]) AS ReportDates
INTO
tblDates
FROM
tblDaysForReport
WHERE
(((tblDaysForReport.Num)<=[Enter Number of Days]))
ORDER BY
tblDaysForReport.Num;
__________________________________________________ __
Design a Query to get the Max Report Date
qryMaxReportDate
Code:
SELECT
Max(tblDates.ReportDates) AS MaxOfReportDates
FROM
tblDates;
__________________________________________________ __
Design a Query which incorporates the Max Report Date. The purpose of this Query is to generate the Completed Date as Max Report Date for those Loans which do not have Completed Date.
qryDueDatesCompletedDatesMaxOfReportDates
Code:
SELECT
tblLoans.EmployeeAssigned,
tblLoans.Loan,
tblLoans.Step,
tblLoans.DueDate,
tblLoans.CompletedDate,
qryMaxReportDate.MaxOfReportDates,
Nz([CompletedDate],[MaxOfReportDates]) AS NewCompletedDate
FROM
tblLoans, qryMaxReportDate;
__________________________________________________ __
Design a Query to return the Employee Assigned, the Loan & the Step for each day from Start Date to Completed Date
qryDueCompletedDates_1
Code:
SELECT
tblDates.ReportDates,
qryDueDatesCompletedDatesMaxOfReportDates.EmployeeAssigned,
qryDueDatesCompletedDatesMaxOfReportDates.Loan,
qryDueDatesCompletedDatesMaxOfReportDates.Step,
qryDueDatesCompletedDatesMaxOfReportDates.DueDate,
qryDueDatesCompletedDatesMaxOfReportDates.CompletedDate,
qryDueDatesCompletedDatesMaxOfReportDates.NewCompletedDate
FROM
tblDates
LEFT JOIN
qryDueDatesCompletedDatesMaxOfReportDates
ON
(tblDates.ReportDates>=qryDueDatesCompletedDatesMaxOfReportDates.DueDate)
AND
(tblDates.ReportDates<=qryDueDatesCompletedDatesMaxOfReportDates.NewCompletedDate);
__________________________________________________ __
Design a Query to Get the Count of Loans for each Employee
qryDueCompletedDatesFinal_1
Code:
SELECT
qryDueCompletedDates_1.ReportDates,
qryDueCompletedDates_1.EmployeeAssigned,
Count(qryDueCompletedDates_1.Loan) AS CountOfLoan
FROM
qryDueCompletedDates_1
GROUP BY
qryDueCompletedDates_1.ReportDates,
qryDueCompletedDates_1.EmployeeAssigned
ORDER BY
qryDueCompletedDates_1.ReportDates,
qryDueCompletedDates_1.EmployeeAssigned;
__________________________________________________ __
Design a Query which gives you the results of the above query the way you want it (Cross Tab)
This is the 2nd Query that is to be run finally.
qryDueCompletedDatesFinal_1_Crosstab_1
Code:
TRANSFORM Sum(qryDueCompletedDatesFinal_1.CountOfLoan) AS SumOfCountOfLoan
SELECT
qryDueCompletedDatesFinal_1.EmployeeAssigned
FROM
qryDueCompletedDatesFinal_1
GROUP BY
qryDueCompletedDatesFinal_1.EmployeeAssigned
ORDER BY
qryDueCompletedDatesFinal_1.EmployeeAssigned, qryDueCompletedDatesFinal_1.ReportDates
PIVOT qryDueCompletedDatesFinal_1.ReportDates;
__________________________________________________ __
Note : Only these 2 Queries have to be run. The rest are sub-queries & should only be designed & ready in your db.
qryGenerateReportDates
qryDueCompletedDatesFinal_1_Crosstab_1
__________________________________________________ ___
Have deliberately kept the process longish, so that you can pinpoint any flaw in the logic & correct it.
If you find it helpful, I am sure with a bit of effort, you should be able to trim it to at least half the size or find an alternative shorter method.
Thanks