Convert currency with a yearly changing factor

[Viktor Harder]

Hello

I have a table with a cost field in USD.
I want to convert the USD in EUR with a factor that is valid for year 2006.
In year 2007 the conversion factor will be different. I have two tables.

Table "Projects"
NAME COST_USD
A 10'000
B 20'000
C 5'000
D 30'000

Table "Rates"
YEAR EUR_USD
2006 1.400
2007 1.350

I would like to create a query shows projects in USD and EUR:

Query "Projects_2006"
NAME COST_USD COST_EUR
A 10'000 7'143 (calculated by
10'000/1.400= 7'143)
B 20'000 14'286
C 5'000 3'571
D 30'000 21'429

Next year I want to use the conversion factor for 2007.

I wanted to create a UNION query. However I did not come to a solution.
Any help is appreciated.
Viktor

 

[JohnFol]

Presuming the table Projects does NOT have a date field to indicate when the
cost was applicable, and you are showing how a project's cost would change
over the years, you could do something like

Select [NAME], COST_USD, DLookup("COST_EUR", "Rates", "YEAR = 2006") *
COST_USD as COST_EUR
union all
Select [NAME], COST_USD, DLookup("EUR_USD", "Rates", "YEAR = 2005") *
COST_USD as COST_EUR

Or even better (and I've not tested this)

Bring both tables into the query grid. Do NOT link them together so you end
up witl all combinations. Somethign like

Select [NAME], COST_USD, COST_USD * EUR_USD as COST_EUR, [Year] from
Projects, Rates order by [year], [name]

 

[Viktor Harder]

Hello John
I have tested the second solution. It worked perfectly.
Thank you very much.
Kind regards
Viktor

Presuming the table Projects does NOT have a date field to indicate when the
cost was applicable, and you are showing how a project's cost would change
over the years, you could do something like

Select [NAME], COST_USD, DLookup("COST_EUR", "Rates", "YEAR = 2006") *
COST_USD as COST_EUR
union all
Select [NAME], COST_USD, DLookup("EUR_USD", "Rates", "YEAR = 2005") *
COST_USD as COST_EUR

Or even better (and I've not tested this)

Bring both tables into the query grid. Do NOT link them together so you end
up witl all combinations. Somethign like

Select [NAME], COST_USD, COST_USD * EUR_USD as COST_EUR, [Year] from
Projects, Rates order by [year], [name]

Hello

I have a table with a cost field in USD.
I want to convert the USD in EUR with a factor that is valid for year
2006.
In year 2007 the conversion factor will be different. I have two tables.

Table "Projects"
NAME COST_USD
A 10'000
B 20'000
C 5'000
D 30'000

Table "Rates"
YEAR EUR_USD
2006 1.400
2007 1.350

I would like to create a query shows projects in USD and EUR:

Query "Projects_2006"
NAME COST_USD COST_EUR
A 10'000 7'143 (calculated by
10'000/1.400= 7'143)
B 20'000 14'286
C 5'000 3'571
D 30'000 21'429

Next year I want to use the conversion factor for 2007.

I wanted to create a UNION query. However I did not come to a solution.
Any help is appreciated.
Viktor