Need help with query to analyse Help Desk Calls

[charlieB]

I need to create a monthly report and graph which will show the total number of Help Desk calls completed for the monyth. The total number of calls needs to be analysed to show the number of calls which were completed within the due date time established by the priority given to each call, and the number of calls that were completed that missed their due date time. I could run 2 queries but would prefer to run one query if this is possible. Thanks - John

 

[MichaelRed]

John/charlieB,

I don't think you have provided sufficient info to get a detailed response to your inquiry. Form a BRIEF glance at your requirement, I would probably do a set of 'layered' queries. Each of the individual sets of info 'look like' they would be easily developed in a crosstab query with parameters for the statr and end dates (these need to be FORCED to have a col for each date within the daterange). A final query would just join each of these, so that the single 'dataset' is available for your reporting.

I have done a call center database in the past, and in that app, I found that reporting the various stats in graphical form was much easier from the indivieual queries noted above. The 'composit' query was really only useful for tabulations.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[braindead2]

I do a report similar to this. I do one query that brings in all the calls for the month. I then use dcount() to count the criteria. I also am able to calculate a % based on the counts

 

[charlieB]

Thanks for your fast response Michael, I concur with your footnote. At times I suspect it gets down to perception and experience. You may believe you have explained it ok, but to the experienced ones like yourself, you missed the boat ! This is my first venture with the forum so I am not sure what the protocol is. From your response it looks like you have a good idea of what I should do. It would be appreciated if you could let me know where I could find an example of the "layered" queries you referred to. Sorry to sound so useless, it reflects the different skill levels we have. John

 

[MichaelRed]

"Layered queries" simply means not doing the whole process in a single step. Often, a similar results set is desired/required form the source data and you can get the individual results in single data sets and then join them so that individual rows of the final results represent the single results. This usually requires a "row heading" to identify the individual results. A somewhat simplistic example follows:


SELECT Format([Date Received],"yy/mm&quot AS Mnth, [Basic Data].SupvDept AS Dept, [Basic Data].Qi AS NumCalls
FROM [Basic Data]
WHERE (((Format([Date Received],"yy/mm&quot) Between Format(DateSerial(Year(Date()),Month(Date()),0),"yy/mm&quot And Format(DateAdd("m",-6,DateSerial(Year(Date()),Month(Date()),1)),"yy/mm&quot))
ORDER BY Format([Date Received],"yy/mm"

TRANSFORM Count(qSelCallsByDept.NumCalls) AS NumCalls
SELECT Format([Mnth],"mmm"", ""yy&quot AS Month, Count(qSelCallsByDept.NumCalls) AS TotCalls
FROM qForceSupvDept LEFT JOIN qSelCallsByDept ON qForceSupvDept.SupvDept = qSelCallsByDept.Dept
GROUP BY Format([Mnth],"mmm"", ""yy&quot, qSelCallsByDept.Mnth
ORDER BY qSelCallsByDept.Mnth
PIVOT qForceSupvDept.SupvDept;

SELECT tblSupvDept.SupvDept, 1 AS Num
FROM tblSupvDept;


SELECT qXTabCallsByDept6Mnth.Month, [JA3111100]/[TotCalls] AS Dept1100, [JA3111200]/[TotCalls] AS Dept1200, [JA3111300]/[TotCalls] AS Dept1300, [JA3111400]/[TotCalls] AS Dept1400, [JA3111700]/[TotCalls] AS Dept1700, [JA3112100]/[TotCalls] AS Dept2100, [JA3112200]/[TotCalls] AS Dept2200, [JA3112300]/[TotCalls] AS Dept2300, [JA3112400]/[TotCalls] AS Dept2400, [JA3112500]/[TotCalls] AS Dept2500, [JA3112600]/[TotCalls] AS Dept2600, [JA3113200]/[TotCalls] AS Dept3100, [JA3113300]/[TotCalls] AS Dept3300
FROM qXTabCallsByDept6Mnth
WHERE (((qXTabCallsByDept6Mnth.TotCalls)<>0 And Not (qXTabCallsByDept6Mnth.TotCalls) Is Null));


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over