Deduct Percentage if Value In Between

[TehNoob2010]

Hey guys,

I need some help with this query, I'm not quite sure what would be the most efficient / easiest way of doing so.

I have the following query:
2w32syt.png
\1
hv2mx4.png
\1

Okay so what I want to be able to do if MINUS a certain percentage from GrossPay if the GrossPay is in between 2 certain values.

For example, if GrossPay were inbetween $400 and $500 (including ($400 and $500) deduct 10% from the GrossPay.
I would like to make a table of values in between and percentages.

Eg values inbetween:
2iurkg1.png
\1

How could I create another table, so that for example when I wanted to change the deduction rate of $400 to $501 I could do so just by changing a eg a value in a Field in a table, instead of changing all this code in a query IIf statement.

Or will I just have to hard code this as an IIf statement in a query, and whenever I want to change the percentage deduction of a certain value I will just have to edit this code. I am really hoping that I could avoid this, as I prefer the table approach which would allow me to change percentages on the fly.

Thanks

[K m]

Assume:
Cell A1 = Gross Pay
Cell A2 = lower limit {In this case,$400}
Cell A3 = upper limit {In this case,$500}
Cell A4 = deduction percentage {In this case,10%}


=IF(AND(A1>=A2,A1<=A3),A1*(1-A4),"")

[TehNoob2010]

Assume:
Cell A1 = Gross Pay
Cell A2 = lower limit {In this case,$400}
Cell A3 = upper limit {In this case,$500}
Cell A4 = deduction percentage {In this case,10%}


=IF(AND(A1>=A2,A1<=A3),A1*(1-A4),"")

I've decided to take the hardcode approach of the IIf statement. I guess I won't really be needing to change the deduction values.

Okay now to my request, how would I do an IIf statement like this?

x=GrossPay btw

If
x>=400 but x<500
Then
10%
Else
x >= 500 but x<600
Then
15%

Etc, etc

[K m]

=if(and(gross_pay>=400,gross_pay<=500),gross_pay*.10,if(and(gross_pay>=501,gross_pay<=600),gross_pay*.15,""))

[Cutter]

How about

=IF(INT(gross_pay/100)=4,gross_pay*0.1,IF(INT(gross_pay/100)=5,gross_pay*0.15,""))

[TehNoob2010]

How about

=IF(INT(gross_pay/100)=4,gross_pay*0.1,IF(INT(gross_pay/100)=5,gross_pay*0.15,""))

Okay now I've checked and I have to put these values and deductions in a table.

So could I make a table with each


tblDeductions

Lower----Upper----Percentage
300------400------10%
401------500------12%
501------600------15%
600------700------18%
701------800------20%

So how could I do so if I had a table above. And if the Gross Value were in between Lower and Upper to return that percentage.

[Cutter]

You've changed your limits from post #3, but using the values in post #6:

Assuming gross pay in cell A1 and table in AA1:AC6

=A1*LOOKUP(A1,AA2:AA6,AC2:AC6)

Make sure you set your limits with cents in mind

[TehNoob2010]

You've changed your limits from post #3, but using the values in post #6:

Assuming gross pay in cell A1 and table in AA1:AC6

=A1*LOOKUP(A1,AA2:AA6,AC2:AC6)

Make sure you set your limits with cents in mind

So if the GrossPay, calculated in the original query is inbetween any of these two values, return the percentage and call the field PercentageReduction. Then I can just easily do another field in the query and easily calculate GrossPay - (PercentageReduction/100)

\1

[TehNoob2010]

Is there a way I could do this without SQL?

Because I have very little knowledge with SQL.

If it helps, here is the Access file.
I use Access 2010.

http://www.mediafire.com/download.php?j122lhs4wc7ndol


Okay I'm not sure with SQL, but if you look on my file above, the GrossPay (PayRate * Hours) is in a Query called qryStaff.

So I tried:

Please Login or Register  to view this content.

But it did not work. :S Why is that?

Yes I know that it's not in percentage form, I just want to test if it actually works first.