View Full Version : Query Problem in Access

19-10-2005, 06:55 PM
Hi, I am having a problem trying to work out the formula using the IF function in MS Access for a query for the Salesperson Commission....

I have done it in Excel and the formula is as follows:

IF(e17>10000,(10000*4%)=(e17-10000)*2%,e17*4%) (this works in Excel)

The commission is made up of 4% of the first 100000 of sales plus 2% of the amount over $10000.

How can I do that in Access? Would appreciate any suggestions as what ever I have put in - it comes up as you are using an operator without an operand or you have + as an illegal syntax.


19-10-2005, 08:44 PM

You could try the "iif" statement in Access... maybe something like this...

Commission: iif([Sales]<=100000, [sales]*0.04, 40000+([sales]-100000)*0.02))

Where [Sales] is the name of the Sales field.

Let me know how you get on!

23-10-2005, 09:10 AM
Thanks for that...didn't quite work correctly. I forgot to mention that the sales commission is per month....

24-10-2005, 09:52 PM
No worries!

Can you give me a few more details re the fields in your table/s and I'll give it another go...


25-10-2005, 03:37 PM
Do you want to post the SQL for the query you have so far? You can get to the SQL when in the query design screen by clicking on View - > SQL View. If you want this for each month, have you tried grouping by month?

25-10-2005, 04:40 PM
Hi Andrew - yes I need to group it for each month - how do you do it??

I had a look at what you suggested...View SQL - sorry its mumbo jumbo to me at the moment....


25-10-2005, 09:33 PM
Hi Averill

I suspect you first need to view the totals in your query. You can do this when you are in the query design screen and clicking on View and then clicking on Totals. Once you have done that, add the month (or date) field to your query and in the totals row you should see 'Group By'. This will group by month - if it hasn't you can change the date field to =Month([YourDateFieldName]). If you have already entered a formula per the previous suggestion, then under that formula you will probably need to change 'Group By' to 'Expression'.

If you can't get this to work, then the way to show us the SQL (the query language) is to go into the query design screen, click on View, then click on 'SQL View' and you will see a whole lot of mumbo jumbo. Copy that mumbo jumbo and paste it into your answer. It will give us a lot of clues as to your table name, field name(s), what you are trying to achieve and so forth.

HTH, Andrew