working with formulas ...new post!!

[Guest]

I was very vague on my previous post so here are more details...Please
remember I don't have a lot of experience with access!.

I have three columns in my table like

C1 C2 C3
USD 1000 X
SGD 200 X
GBP 500 X
AUD 200 X

So I’ll need to “add a formula†in C3 to display specific value (currency)
dependng on the text entered on C1, then I'll have to multiply values in C2
for a determined rate so it'd be something like this???:

IF([C1]=â€USDâ€,[C2]*.98)OR ([C1]=â€SGDâ€,[C2]*.50)OR ([C1]=â€AUDâ€,[C2]*2,0)OR
([C1]=â€USDâ€,[C2]>>>>For USD C3 would have to be equal to C2

I know how to do this with Excel, but I don’t even know if this can be done
with access. Maybe running a query? I’d do the table again and paste it from
Excel but it has more than 600,000 records and it’ll be a pain.

Please help!

 

[Pieter Wijnen]

Create a Rate Table
With
Currency Rate
USD .98
SGD .50

Then Create a Query
SELECT R.Currency, M.C2, R.Rate * M.C2 As TheValue FROM
Rate R Inner Join MyTable M On R.Currency = M.C1

HtH

Pieter

 

[Guest]

Hi Angelica

Assuming that c1 and c2 are field in a table called table1.

Create a new query and bring in table1.
in SQL view paste this

SELECT
IIf([Table1]![c1]="usd",[Table1]![c2]*0.98,IIf([Table1]![c1]="sgd",[Table1]![c2]*0.5,IIf([Table1]![c1]="aud",[Table1]![c2]*2,IIf([Table1]![c1]="gbp",[Table1]![c2]*2)))) AS c3
FROM Table1;

or

in a blank column paste this into the Field row (the top ro of the column)

c3:
IIf([Table1]![c1]="usd",[Table1]![c2]*0.98,IIf([Table1]![c1]="sgd",[Table1]![c2]*0.5,IIf([Table1]![c1]="aud",[Table1]![c2]*2,IIf([Table1]![c1]="gbp",[Table1]![c2]*2))))

Just a point - I would not store the result of the calculation as if the %
changes you will have lots of wrong results.

Also a better idea would be to store the % in a seperate table and then use
this new table (field) to give the % for the calculation in the query. You
could include a date field in the new table and use this to ensure that any
quotes a correct at the time they are given etc.etc.

Hope this helps