Query - Count of Cases and Count of where Resolution Not Completed

[mralmackay]

Hi,

Hopefully you can help with this. I've done the query below to show
records in our call logging system where a case has been completed by
Month. This correctly shows this data.

However, I need to be able to change two things on it but can't work
out what I need to do:

1) This shows it by Month Number (i.e. 1 for Jan, 2 for Feb etc..).
Would be better if this could possible show shortened name for the
Month (e.g. Jan; Feb; Mar onwards....)
2) I also need to show the total cases against each month as this
only shows how many records have a resolution populated but would like
it to show how many cases in that month and then show in a further
column how many of them cases have a resolution populated.

For example:
Assigned to Group Create Year Jan-Total Jan-Resolution
Group 1 2008 80 60

Is this possible? Appreciate help on this as completely stuck.

Thanks, Al.

Query:
TRANSFORM Count([P2P-Request].[Resolution-Detail]) AS
[CountOfResolution-Detail]
SELECT [P2P-Request].[Assigned-To-Group], Year([Create-Date]) AS
CreateYear
FROM [P2P-Request]
WHERE (((Year([Create-Date]))=2008))
GROUP BY [P2P-Request].[Assigned-To-Group], Year([Create-Date])
PIVOT DatePart("m",[Create-Date]);

 

[John Spencer]

If you want all twelve months to appear you can use an In clause in the
PIVOT clause

TRANSFORM Count([P2P-Request].[Resolution-Detail]) AS
[CountOfResolution-Detail]
SELECT [P2P-Request].[Assigned-To-Group]
, Year([Create-Date]) AS CreateYear
FROM [P2P-Request]
WHERE (((Year([Create-Date]))=2008))
GROUP BY [P2P-Request].[Assigned-To-Group], Year([Create-Date])
PIVOT DatePart("mmm",[Create-Date]) IN ("Jan","Feb", "Mar", "Apr", "May",
"Jun", "Jul","Aug","Sep","Oct","Nov","Dec")

Cross tabs are not good at putting two values in a "cell", but you can try

TRANSFORM Count([P2P-Request].[Resolution-Detail]) & CHr(13) & Chr(10) &
Count(*)
SELECT ...

I don't know if the second suggestion will work.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..