Help with formula 2

Juan0214 · July 10th, 2008, 04:05 PM

I am working on an incentive database. In this database,

I have three tableâs name [Transaction Errors], [Potential Payout] and
[Actual Incentive Calculation]. If [Transaction Errors] is greater than 6
errorâs then [Actual Incentive Calculation] will be $0.00, but if [Transaction
Errors] is less than 6 errors then [Actual Incentive Calculation] we be
whatever amount is in [Potential Payout].

Can this be done? Iâm really new at this and Iâm trying to learn as I go so
please let me know if thereâs any other information you need from me to
figure this out. Thanks so much.

Old Pedant · July 10th, 2008, 04:53 PM

You named the TABLES there, but what *fields* within those tables have the characteristics you are talking about?

Yes, this shouldn't be hard to do, at all, if you give a tiny bit more details. Try to show us all the *relevant* fields in each of the 3 tables. E.g., employeeid, errortype, whatever.

Juan0214 · July 10th, 2008, 05:08 PM

I meant fields not tables

Old Pedant · July 10th, 2008, 06:52 PM

Oh, even easier then!

You don't give your other fields in the table, but I'll assume that you have employeeID and employeeName in there, too. Since you presumably want the payout for each employee.

Code:

SELECT employeeID, employeeName, 
       IIF( [Transaction Errors] > 6, 0.0, [Potential Payout] ) As ActualPayout
FROM table
ORDER BY employeeName

If you mean that you want to UPDATE the table to change the value of the [Potential Payout] field to match, then that's just as easy:

Code:

UPDATE table
SET [Actual Incentive Calculation] = IIF( [Transaction Errors] > 6, 0.0, [Potential Payout] )

This all *assumes* that you have ONE RECORD in this table per employee. If that's not true, then you need to give many more details, possibly including some sample data (okay if it's dummy).