help doing an IF THEN sort of thing

  • Thread starter Thread starter Notch Johnson
  • Start date Start date
N

Notch Johnson

My dilemma, I'm trying to create a Comission Report for
my firm, pulling the data from our accounting software.

The only thing I can't figure out how to do is: if the
practice class = 10, then the commission would be $125
but if the practice class = 04 then the commission is 25%
of the fee paid, while all other practice classes have a
commission of 20% of the fee paid. Is that making sense
to anyone?

Here is my query:
SELECT Right([CliCode],6) AS Expr1, Client.CliNickName,
Matter.MatCode, Matter.MatNickName, Employee.EmpName, Sum
(FeeSumByPrd.FSPFeeReceived) AS SumOfFSPFeeReceived,
Matter.MatPracticeClass
FROM (((Matter INNER JOIN Client ON Matter.MatCliNbr =
Client.CliSysNbr) INNER JOIN MatOrigAtty ON
Matter.MatSysNbr = MatOrigAtty.MOrigMat) INNER JOIN
FeeSumByPrd ON MatOrigAtty.MOrigMat =
FeeSumByPrd.FSPMatter) INNER JOIN Employee ON
MatOrigAtty.MOrigAtty = Employee.EmpSysNbr
GROUP BY Right([CliCode],6), Client.CliNickName,
Matter.MatCode, Matter.MatNickName, Employee.EmpName,
Matter.MatPracticeClass, Employee.EmpInitials,
FeeSumByPrd.FSPPrdNbr, FeeSumByPrd.FSPPrdYear
HAVING (((Sum(FeeSumByPrd.FSPFeeReceived))<>0) AND
((Employee.EmpInitials)=[Initials:]) AND
((FeeSumByPrd.FSPPrdNbr)=[Month:]) AND
((FeeSumByPrd.FSPPrdYear)=[Year:]))
ORDER BY Right([CliCode],6), Matter.MatCode;
 
Hi Notch,

Try an expression like this in the field list. I assume that
Matter.MatPracticeClass is a text field (because of the leading zero)
with the practice class and Sum(FeeSumByPrd.FSPFeeReceived) is the
expression that returns the fee from which to calculate the commission.

IIF(Matter.MatPracticeClass="04", 125,
IIF(Matter.MatPracticeClass="10", 0.25, 0.2) *
Sum(FeeSumByPrd.FSPFeeReceived)) AS Commission



My dilemma, I'm trying to create a Comission Report for
my firm, pulling the data from our accounting software.

The only thing I can't figure out how to do is: if the
practice class = 10, then the commission would be $125
but if the practice class = 04 then the commission is 25%
of the fee paid, while all other practice classes have a
commission of 20% of the fee paid. Is that making sense
to anyone?

Here is my query:
SELECT Right([CliCode],6) AS Expr1, Client.CliNickName,
Matter.MatCode, Matter.MatNickName, Employee.EmpName, Sum
(FeeSumByPrd.FSPFeeReceived) AS SumOfFSPFeeReceived,
Matter.MatPracticeClass
FROM (((Matter INNER JOIN Client ON Matter.MatCliNbr =
Client.CliSysNbr) INNER JOIN MatOrigAtty ON
Matter.MatSysNbr = MatOrigAtty.MOrigMat) INNER JOIN
FeeSumByPrd ON MatOrigAtty.MOrigMat =
FeeSumByPrd.FSPMatter) INNER JOIN Employee ON
MatOrigAtty.MOrigAtty = Employee.EmpSysNbr
GROUP BY Right([CliCode],6), Client.CliNickName,
Matter.MatCode, Matter.MatNickName, Employee.EmpName,
Matter.MatPracticeClass, Employee.EmpInitials,
FeeSumByPrd.FSPPrdNbr, FeeSumByPrd.FSPPrdYear
HAVING (((Sum(FeeSumByPrd.FSPFeeReceived))<>0) AND
((Employee.EmpInitials)=[Initials:]) AND
((FeeSumByPrd.FSPPrdNbr)=[Month:]) AND
((FeeSumByPrd.FSPPrdYear)=[Year:]))
ORDER BY Right([CliCode],6), Matter.MatCode;
Click to expand...
 
Back
Top