Get total number of records within a crosstab query

  • Thread starter Thread starter Mobiius
  • Start date Start date
M

Mobiius

Hello,

I have a crosstab query for my contact center which will give me the total
number of incidents which have been resolved at first contact sorted by site,
and by incident creation method.

Basically, the column headers contain the incident creation types:
Telephone, Email, and Web, along with the total numbers of these 3 methods
added up.
The row headers are sites. (In my DB, there are only 3 sites, MH, GH and all
the others, which are blank)

This gets its information from another query which sorts all the data to
provide only the information I require. (Including currently the criteria of
selecting FTF (first tie fix) and non FTF incidents)

What I need to do and have been unable to find out how to do is as well as
crosstabbing FTF incident counts by creation method and by site, I'd also
like a further column header which shows the TOTAL number of incidents. FTF
and non FTF in order to create a percentage value.


Finally, my question is, Is there a way to display the crosstab information
counts of FTF, and have the total number of incidents by site also? I know
that I cant include all data and use a where clause as Jet complains that I
cannot use an aggregate function within a where clause.

The SQL which works but doesn't give me total incidents is:

TRANSFORM Count([All Data Query].Ref) AS CountOfRef
SELECT [All Data Query].Site, MonthName([Month]) AS Month_, Count([All Data
Query].FTF) AS CountOfFTF
FROM [All Data Query]
GROUP BY [All Data Query].Site, [All Data Query].Month, MonthName([Month]),
[All Data Query].Year
ORDER BY [All Data Query].Month
PIVOT [All Data Query].[Creation Method];


And the SQL I'd LIKE to be able to use is:

TRANSFORM Count([All Data Query].Ref) AS CountOfRef
SELECT [All Data Query].Site, MonthName([Month]) AS Month_, Count([All Data
Query].FTF) AS CountOfFTF
FROM [All Data Query]
WHERE (((Count([All Data Query].FTF))=1))
GROUP BY [All Data Query].Site, [All Data Query].Month, MonthName([Month]),
[All Data Query].Year
ORDER BY [All Data Query].Month
PIVOT [All Data Query].[Creation Method];


The SQL for the query the crosstab gets it's data from is also below (Which
doesn't seem to be filtering months or dates for some reason):

SELECT [Imported Data].Ref, [Imported Data].Service, [Imported
Data].[Creation Method], [Imported Data].[RG at Resolve], [Imported
Data].FTF, [Imported Data].[Resolver ID], Creators.Site, [Imported
Data].Month, [Imported Data].Year
FROM Creators INNER JOIN [Imported Data] ON Creators.CreatorID = [Imported
Data].[Resolver ID]
WHERE ((([Imported Data].Service)="DII/F") AND (([Imported Data].[RG at
Resolve])="CIS SPOC" And ([Imported Data].[RG at Resolve])="CIS SPOC") AND
(([Imported Data].Month)="5") AND (([Imported Data].Year)="9")) OR
((([Imported Data].[RG at Resolve])="CIS SPOC TPA")) OR ((([Imported
Data].[RG at Resolve])="DIIF Password-Awaiting authori")) OR ((([Imported
Data].[RG at Resolve])="DIIF Password-Awaiting Closure")) OR ((([Imported
Data].[RG at Resolve])="DLP")) OR ((([Imported Data].[RG at Resolve])="HPT"))
OR ((([Imported Data].[RG at Resolve])="HPT DII/C")) OR ((([Imported
Data].[RG at Resolve])="HPT UOD")) OR ((([Imported Data].[RG at
Resolve])="SPOC ACD Admin"));
 
Thanks for your reply. However it didn't solve the issue. It only counted
records where is wasn't null (Which matched the count of , but I need to
provide totals for FTF = 1 by site for the crosstab, as well as where FTF is
not null by site.

And the other sql didn't run at all, even after I fixed some issues in it.
And the non crosstab query still doesn't display only the correct month or
year data. It STILL shows all data!
 
right, I've solved the not sorting by date issue. The issue was in the whare
clause. it was showing records where service=dii/f AND Month=X AND Year=X AND
RG="CIS SPOC" OR the other records.

I split the query into 3 seperate ones each one doing it's own filter so by
the time it gets to the crosstab, it shows the correct information.

Now I just need to count all true FTF records by site as well as count all
FTF records by site.
 
Try this --
TRANSFORM Count([All Data Query].Ref) AS CountOfRef
SELECT [All Data Query].Site AS Expr1, MonthName([Month]) AS Month_,
Count([All Data Query].FTF) AS CountOfFTF, Count([All Data Query].Ref) AS
Total, Sum(IIF ([All Data Query].[FTF] =1, 1, 0)) AS FTF_Count
FROM [All Data Query]
GROUP BY [All Data Query].Site, [All Data Query].Month, MonthName([Month]),
[All Data Query].Year
ORDER BY [All Data Query].Month
PIVOT [All Data Query].[Creation Method];
 
Ok then, thanks Karl. Although your SQL didn't provide the results needed, I
did alter it to provide the information I required!

Here it is...

TRANSFORM Sum(IIf([03 - Query By Date].[FTF]=1,1,0)) AS FTF_Count
SELECT [03 - Query By Date].Site, Sum(IIf([03 - Query By Date].[FTF]=1,1,0))
AS FTFs, Count([03 - Query By Date].Ref) AS Total
FROM [03 - Query By Date]
GROUP BY [03 - Query By Date].Site
PIVOT [03 - Query By Date].[Creation Method];


I only have one final question!! (Sorry :) )

I'd like to include a percentage of FTFs to Incidents.
I tried adding the expression:
Sum(IIf([03 - Query By Date].[FTF]=1,1,0)) / [03 - Query By Date].[Ref]*100
But again, received an error.
I've probably missed as easy solution here, in which case I'm wondering what
this solution would be.

Thanks sooo much for your help so far!
 
Back
Top