Leroy Leroy - 4 years ago 158
SQL Question

Select flag to indicate if each field in bottom 10 percent in access sql

I have a table which lists scores for each employee across a range of performance areas (KPIs). I have written a query which returns these scores as well as 'flag' fields indicating if they were in the bottom 10 percent for each KPI per business area. The query is working but seems overly complex (the example below shows 3 KPIs but the actual version has many more):

SELECT [Employee Number], [Full Name], [Business Area],

[Absence],
IIF([Employee Number] in (
SELECT TOP 10 PERCENT [Employee Number]
FROM tblKPIScores as a
WHERE a.[Business Area] = tblKPIScores.[Business Area]
ORDER BY Absence DESC
),"Y","N") AS AbsenceFlag,

[Complaints],
IIF([Employee Number] in (
SELECT TOP 10 PERCENT [Employee Number]
FROM tblKPIScores as b
WHERE b.[Business Area] = tblKPIScores.[Business Area]
ORDER BY Complaints DESC
),"Y","N") AS ComplaintsFlag,

[Service Time],
IIF([Employee Number] in (
SELECT TOP 10 PERCENT [Employee Number]
FROM tblKPIScores as c
WHERE c.[Business Area] = tblKPIScores.[Business Area]
ORDER BY [Service Time] DESC
),"Y","N") AS [Service Time Flag]

FROM tblKPIScores


I was wondering if there is a more efficient way of writing this instead of having to do a separate sub query for each KPI?

Answer Source

Consider using derived tables, one for each KPI. You can even save each derived table as a separate saved query, replacing the nested SELECT statements in LEFT JOIN clauses. This would be a more efficient solution as no longer do you run correlated subqueries nested in IIF() for each row value.

Note: Access SQL requires parentheses wrapped for each JOIN parings; hence for complex queries it is better to predesign joins in query's Design View:

SELECT t.[Employee Number], t.[Full Name], t.[Business Area], 
       t.[Absence], IIF(a.AbsenceEmpNum IS NOT NULL, 'Y', 'N') AS AbsenceFlag,
       t.[Complaints],  IIF(a.ComplaintsEmpNum IS NOT NULL, 'Y', 'N') AS ComplaintsFlag,
       t.[Service Time], IIF(a.ServiceTimeEmpNum IS NOT NULL, 'Y', 'N') AS ServiceTimeFlag

FROM ((tblKPIScores t

LEFT JOIN
   (SELECT TOP 10 PERCENT sub.[Employee Number] As AbsenceEmpNum
    FROM tblKPIScores as sub
    ORDER BY sub.Absence DESC) AS a
ON t.[Employee Number] = a.AbsenceEmpNum)

LEFT JOIN
   (SELECT TOP 10 PERCENT sub.[Employee Number] As ComplaintsEmpNum
    FROM tblKPIScores as sub
    ORDER BY sub.Complaints DESC) AS c
ON t.[Employee Number] = c.ComplaintsEmpNum)

LEFT JOIN
   (SELECT TOP 10 PERCENT sub.[Employee Number] As ServiceTimeEmpNum
    FROM tblKPIScores as sub
    ORDER BY sub.[Service Time] DESC) AS s
ON t.[Employee Number] = s.ServiceEmpNum
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download