Solved

MS Access query: Return a value based on the value in another field

Posted on 2012-04-10
4
381 Views
Last Modified: 2012-04-11
IFTHENExample-2012-04-10.accdbI am trying to write a query (see qryGMUtility) that returns the commission that we pay on each product sales record.  The commissions we pay are stored in a table called “UtilityPayout”.  
Commissions are only paid when the orders are accepted by the utility, that is, the UtilityStatus="Accepted by Utility".  Therefore, the value in the field “Payout” should be $0 if the UtilityStatus=”Rejected by Utility”, otherwise, the payout value should be  taken from the UtilityPayout table.
Here’s one of the things that I tried without success: IIf([qrytblSalesToo].[UtilityStatus]="Rejected*",0,[Payout])
Attached is a small extract of the db with the query.
Thanks for your help and any pointers that you can give me to help me learn.
BTW, does anyone know why "TCP/IP" is showing up as a topic under "Select Topics" where I submit this question?  I can't get rid of it.
0
Comment
Question by:thutchinson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 37831152
See qryGMUtility of attached file, Commission Field. If you want the query to return only paid commissions, put ">0" (without quotes) in the criteria section.

Flyster
IFTHENExample-2012-04-10.accdb
0
 

Author Comment

by:thutchinson
ID: 37832446
I want all the records returned (accepted and rejected).  I want the value in PAYOUT to be -0- when the order is rejected.  I want the Payout value if the order is accepted.
0
 

Author Comment

by:thutchinson
ID: 37833113
OK, I'm able to get the Gross Margin on all sales if I make a couple of changes to the code you suggested. it works now like this:

CommissionPayout: IIf([UtilityStatus]="Rejected by Utility",0,[Payout])
Margin: [qrytblSalesToo].[Payment]-[CommissionPayout]


Thanks for your help, Flyster.  I really appreciate it!
0
 
LVL 22

Expert Comment

by:Flyster
ID: 37833805
Thank you. I'm glad I was able to assist!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question