query with calculation (1 Viewer)

[adi32]

I have a query with these fields
Field 1 Amount
Field 2 Type (1 2 3 4 5 6)
Field 3 Size (1 2)


I want to calculate the amount in a new field like this
If type =1 and Size =1 then amount=amount*10
If type =2 and Size =1 then amount=amount*8
If type =3 and Size =1 then amount=amount*2.5
If type =4 and Size =1 then amount=amount*4…………………….

If type =1 and Size =2 then amount=amount*10
If type =2 and Size =2 then amount=amount*8
If type =3 and Size =2 then amount=amount*2.5
If type =4 and Size =2 then amount=amount*4…………………….

12 cases

how can I add this in a query ?

thank you

 

[Brianwarnock]

Since size doesn't influence the calculation just nest 6 iifs

IIf(type=1,[amount]*10,iif(type=2,[amount]*8,iif...........etc


Brian

 

[namliam]

How about making a relational table where you store these multiplyers??

Seems to me like this would be more of a lookup-table thingy rather than iIF...Iif...iIf...iIF etc.