Multiple IIF Help Needed

[cruz610]

This code checks to see which FTEID it is currently on (performing calculations). Since I need this in a query I cant use If...Then or Cases thus it is a very nested IIF query and its giving me troubles. Can anyone see any major errors with it? Will this work at all?

SELECT tblClients.numTransactionID, tblFTE.numFTEID, tblClients.numNSCC, tblNSCCMetrics.id, tblNSCCMetrics.numRegReject, tblNSCCMetrics.numTradeReject, tblNSCCMetrics.numNetworkReject, tblNSCCMetrics.numInboundCalls, tblNSCCMetrics.numOutboundCalls, tblFTE.numPMO, IIF([numFTEID]=18, ([numNSCC]*[numInboundCalls])/[numPMO], IIF([numFTEID]=21, ([numNSCC]*[numNetworkReject])/[numPMO], IIF([numFTEID]=24, ([numNSCC]*[numOutboundCalls])/[numPMO], IIF([numFTEID]=27, ([numNSCC]*[numRegReject])/[numPMO]), ([numNSCC]*[numTradeReject])/[numPMO]))))) AS NSCC
FROM tblClients, tblNSCCMetrics, tblFTECat INNER JOIN tblFTE ON tblFTECat.numFTECatID = tblFTE.numFTECatID
GROUP BY tblClients.numTransactionID, tblFTE.numFTEID, tblClients.numNSCC, tblNSCCMetrics.id, tblNSCCMetrics.numRegReject, tblNSCCMetrics.numTradeReject, tblNSCCMetrics.numNetworkReject, tblNSCCMetrics.numInboundCalls, tblNSCCMetrics.numOutboundCalls, tblFTE.numPMO
HAVING (((tblClients.numTransactionID)=[Forms]![frmClients].[numTransactionID]) AND ((tblFTE.numFTEID) In (18,21,24,27,28)) AND ((tblNSCCMetrics.id)=1));

 

[scriverb]

Try using the Switch statement in queries:

Switch( [numFTEID]=18, [numNSCC]*[numInboundCalls])/[numPMO], [numFTEID]=21, [numNSCC]*[numNetworkReject])/[numPMO], [numFTEID]=24, [numNSCC]*[numOutboundCalls])/[numPMO], [numFTEID]=27, [numNSCC]*[numRegReject])/[numPMO], True, [numNSCC]*[numTradeReject])/[numPMO])

I used your specific logic but you can see how this works by looking it up in ACCESS Help. Should work well for you in this instance.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]