Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 10th, 2008, 04:05 PM
Registered User
 
Join Date: Jul 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with formula 2

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 July 10th, 2008, 04:53 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old July 10th, 2008, 05:08 PM
Registered User
 
Join Date: Jul 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I meant fields not tables

 
Old July 10th, 2008, 06:52 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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).





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with formula Juan0214 Access 4 August 1st, 2008 07:25 AM
Formula scandalous Access VBA 11 February 27th, 2007 09:49 AM
formula sinha Crystal Reports 1 October 26th, 2005 12:53 PM
Help with a formula Corey Access 2 October 20th, 2005 10:48 AM
Formula Ned Pro VB 6 2 September 10th, 2003 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.