Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2012-04-10
4
Medium Priority
?
393 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
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

580 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