Robeen
Thanks for your suggestion. It worked out pretty well The crosstab does the trick, but partially. However, that gave me an idea to design the criteria as per my need. I did not try the second idea though, cos I do not find it efficient to create so many queries. If you would be interested, here is the SQL:
Code:
TRANSFORM Count(DBA_cases.casenum) AS CountOfcasenum
SELECT DBA_cases.matcode, Count(DBA_cases.casenum) AS [Total Of casenum]
FROM DBA_cases
WHERE (((DBA_cases.matcode)="AUT" Or (DBA_cases.matcode)="PI" Or (DBA_cases.matcode)="WC" Or (DBA_cases.matcode)="S S"))
GROUP BY DBA_cases.matcode
PIVOT Format([date_opened],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I now have an idea to make it more user friendly by creating a pop up form where in the date range can be chosen, and the report would generate the data for that.