Using multiple criteria in an update query

[Guest]

[Access 03] I have a suppliers price list that I want to amend to my needs.
That is if RRP is <=5 then update RRP*2 AND if RRP is >5,<=10 then update
RRP*1.85 AND if RRP >10, <=20 then update RRP*1.6 on so on. When I run the
first update (RRP <=5 (then*2) the qry returns what I want, but when I run
the second condition, the first update is altered. What am I missng here!?

 

[Tanis]

Post your SQL. We may have a better idea about what's going on.

 

[Arvi Laanemets]

Hi

It's all exactly as you instructed your comp )

P.e. when your RRP was 4, then at 1st update you got 4*2=8. When doing 2nd
update, the same RRP had value 8, what is >5 and <10, and was recalculated
again - 1.85*8=14.80 etc.

When you are doing updates sequently, start form biggest RRP interval.

A better solution though will be to make all updates at one go - using some
lookup table from where appropriate coefficient is read, or calculating it
directly. One possible example for latter (I assume you'll have only 4 range
intervals - <5, >=5 and <10, >=10 and <20, >20 - like in your posting):

..... , (1.5+Iif(RRP>=10 And RRP <20,0.15,0)+Iif(RRP>=5 And
RRP<10,0.15,0)+Iif(RRP<5,0.15,0))*RRP As RRP, ...