SQL IIF statement with TOP 10

C

cuddihy1990

Joined
Feb 26, 2013
Messages
12
Reaction score
0
Before I explain the problem I will post the SQL below;
SELECT Printed_Job_Lot.JobNumber, Employees_Performance.PartName, Employees_Performance.Status,
IIf([Employees_Performance.Status]="BUILT",[Employees_Performance.Builder],
IIf([Employees_Performance.Status]="CUT",[Employees_Performance.Cutter],
IIf([Employees_Performance.Status]="INSPECTION",[Employees_Performance.InspectionReceived],
IIf([Employees_Performance.Status]="REJECTED",[Employees_Performance.Builder],
IIf([Employees_Performance.Status]="INSPECTED",[Employees_Performance.InspectionReceived],"0"))))) AS [Employee Number],
IIf([Employees_Performance.Status]="BUILT",[Employees_Performance.BuilderName],
IIf([Employees_Performance.Status]="CUT",[Employees_Performance.CutterName],
IIf([Employees_Performance.Status]="INSPECTION",[Employees_Performance.InspectionReceivedName],
IIf([Employees_Performance.Status]="REJECTED",[Employees_Performance.BuilderName],
IIf([Employees_Performance.Status]="INSPECTED",[Employees_Performance.InspectorName],"0"))))) AS [Employee Name],
IIf([Employees_Performance.Status]="BUILT",[Employees_Performance.BuildDate],
IIf([Employees_Performance.Status]="CUT",[Employees_Performance.CutDate],
IIf([Employees_Performance.Status]="INSPECTION",[Employees_Performance.ReceivedDate],
IIf([Employees_Performance.Status]="REJECTED",[Employees_Performance.BuildDate],
IIf([Employees_Performance.Status]="INSPECTED",[Employees_Performance.InspectionDate],"0"))))) AS [Date], Employees_Performance.PassFail
FROM Printed_Job_Lot INNER JOIN Employees_Performance ON Printed_Job_Lot.LotNumber=Employees_Performance.LotNumber
WHERE (((Employees_Performance.Status)<>"INSPECTED"));
Click to expand...

Basically what I need done is after each if statement where it selects what to bring back based on Employees_Performance.Status, I only need the first result (i.e. top one)

therefore the line;
IIf([Employees_Performance.Status]="BUILT",[Employees_Performance.Builder],
Click to expand...
should look similar to this;
IIf([Employees_Performance.Status]="BUILT", TOP 1([Employees_Performance.Builder]),
Click to expand...

any ideas?

Thanks in advance
 
Last edited:
Back
Top