Complex Query Issue?

[John Baker]

I have a database that is used to track the closure rates of problem management tickets. My objective is to track metrics (total # of problem tickets by location and customer type, average # of days to close, and 95th percentile). The 95th percentile represents the number of days it took to close a problem management ticket (for each location and customer type) for 95th % of all problem management tickets for a specific location and customer type.

I have developed queries that group the metrics by location, for total # of problem tickets, customer type, and avg # of days to close. I have also developed forms with subforms that visually display the dataset record that represents the 95% percentile. However, I would like to create a query that I could use for a report that I can use for reporting all of the metrics I need.

Thanks in advance for any help or guidance you can provide!

John

 

[John Baker]

More Information

Hi All -

Here is additional information on the problem I am trying to solve. Any help or insights will be appreciated:

I have one table (tbl_ims) that contains all incident tickets that were logged during the month:
INCIDENT_ID
MONTH
LOCATION
CUSTOMER_TYPE
OPEN_TIME
CLOSED_TIME

Next, I have several queries that are used to calculate totals and averages that are grouped by location and customer type:

qry_ims_local
(Groups all of the incident tickets by location and customer type. Also calculates the elapsed time for each incident ticket):
MONTH
LOCATION
CUSTOMER TYPE
OPEN_TIME
CLOSE_TIME
ELAPSED_TIME

qry_get_total_local
(Calculates the total # of incidents for month, location, and customer type)
MONTH
LOCATION
CUSTOMER_TYPE
TotalIM

qry_local_1_calc
(Calcuates the average incident ticket elapsed time for each location and customer type):
MONTH
LOCATION
CUSTOMER_TYPE
AvgOfELAPSED

qry_local_2_get_95th
(Identifies the record number in the dataset that identifies the 95th percentile for each location and customer type):
MONTH
LOCATION
CUSTOMER_TYPE
TotalIM
ninetyfivepctl (Based on the total number of records, this represents the record # of the 95th percentile)

Now, for reporting purposes, I would like to have 1 query that pulls it all together for me. The piece I am missing is pulling the ELAPSED_TIME value for each grouping of LOCATION and CUSTOMER_TYPE. Although I have the record number, I cannot figure out how to go to the record, get the ELAPSED_TIME, and store it for reporting purposes. The best I've been able to do so far is to create a user interface that displays the calculations with a subform that displays the details of the record that represents the 95th percentile.

I hope this makes sense. Any help would be greatly appreciated!

John

 

[John Baker]

Problem Solved With Module Code

Ok, it's been about 10 years since I coded in VB but I decided to take a stab at solving my problem with code. Here is what I came up with - I use one of my queries as the primary driver file and build a table (tbl_ims_report) that I use to create and print a report with all of the metrics that I need. The solution works. Any insights into how this solution may be done more efficiently would be appreciated!

John

Function Report_Build_Local()

Dim db As Database

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim intCounter1 As Integer
Dim intCounter2 As Integer
Dim strMonth As String
Dim strBU As String
Dim strCustomerType As String
Dim strNumberprgn As String
Dim intIM As Integer
Dim varElapsed As Variant
Dim intNinetyFivePctl As Integer
Dim varResult As Variant
Dim strSQL As String

Set db = CurrentDb

Set rs1 = db.OpenRecordset("qry_local_calcs")

rs1.MoveFirst
For intCounter1 = 1 To rs1.RecordCount - 1
strMonth = rs1!MONTH
strBU = rs1!BU
strCustomerType = rs1!CUSTOMER_TYPE
intIM = rs1!TotalIM
varElapsed = rs1!AvgOfELAPSED
intNinetyFivePctl = rs1!ninetyfivepctl

strSQL = "SELECT BU, CUSTOMER_TYPE, NUMBERPRGN, ELAPSED " & _
"FROM qry_local_3_ims " & _
"WHERE qry_local_3_ims.BU = " & """" & strBU & """" & " " & _
"AND qry_local_3_ims.CUSTOMER_TYPE = " & """" & strCustomerType & """" & " " & _
"ORDER BY qry_local_3_ims.ELAPSED"

Set rs2 = db.OpenRecordset(strSQL)

For intCounter2 = 1 To intNinetyFivePctl

If intCounter2 = intNinetyFivePctl Then
varResult = rs2!ELAPSED
strNumberprgn = rs2!NUMBERPRGN

strSQL = "INSERT INTO tbl_ims_report (MONTH, BU, CUSTOMER_TYPE, TotalIM, AvgOfELAPSED, ninetyfivepctl, Result) " & _
"VALUES (" & """" & strMonth & """," & """" & strBU & """," & """" & strCustomerType & """," & intIM & "," & varElapsed & "," & intNinetyFivePctl & "," & varResult & ")"

db.Execute (strSQL)

End If

rs2.MoveNext
Next intCounter2
rs2.Close

rs1.MoveNext
Next intCounter1

rs1.Close

Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Function