Case Aging report

[mavisyew]

Hi All,



I am creating a Case Aging report.
In my report, i have the below fields.

1. CaseID
2. Priority
3. Type
4. Status
5. 0-1 day
6. 2-5 days
7. 6-10 days
8. 11-20 days
9 >20 days

My query as show below.

SELECT TBLCaseDetail.CaseID, TBLCaseDetail.Status, TBLCaseDetail.Priority, TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, DateDiff('d',[ReportedDate],Now()+1) AS NumberOfDays
FROM TBLCaseDetail
WHERE (((TBLCaseDetail.Status)<>"Resolved"));

My Query Select all the cases which is not "Resolved" and count the number of days from Reported date till now.

How can i show a mark if the number of days follow within the group?

Sample:

CaseId	Priority	Type	Status	0-1 Day	2-5 Days	6-10 Days	11-20 Days	> 20 Days
14-00001	Major	Aircon	Pending		X
14-00006	Minor	Toilet	New				X
14-00010	Major	Flooring	Pending					X
14-00020	Major	Lighting	Pending	X

How can i have a report something like that?

Thanks

[JoeM]

Through a series of calculated fields in your query.

For example, for the 6-10 one, it might look something like:

Check6_10: IIF((DateDiff('d',[ReportedDate],Now()+1) >= 6) AND (DateDiff('d',[ReportedDate],Now()+1) <= 10),"X","")