How do I sum an IIF function in an Access Report?

[Guest]

I have created a report that contains a field that has string of IIF
statements (15 to be exact) related to it. This concept of this field is the
following:

I have money in differenct currencies; the IIF statement recognizes what
currency it is then divides it by the exchange rate; this new amount is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements to work,
but in the footer of my report, I want to have a sum of the 'Total in USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF function within a
Report?

Thanks for the help,
Ryan

 

[Duane Hookom]

You shouldn't nest IIf() statements. You should have a table of exchange
rates that you can add to your report's record source. Join this table to
your currency type field and use an "ExchangeRate" column to multiply by you
amount.

 

[Marshall Barton]

I have created a report that contains a field that has string of IIF
statements (15 to be exact) related to it. This concept of this field is the
following:

I have money in differenct currencies; the IIF statement recognizes what
currency it is then divides it by the exchange rate; this new amount is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements to work,
but in the footer of my report, I want to have a sum of the 'Total in USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF function within a
Report?

You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Guest]

Marsh,

I have about 16 different currencies that I use. I need to be able to
convert each one into EURO and DOLLARS. The currencies range from Australia
Dollar to Yen. The original amounts (in original currency) are in the main
table. How can I convert each differenr currency in a query to show in a
report. It seems to me I would still have to use a long nested IIF function.
Correct? I hope I am wrong and there is some other easier way to do this.
Please let me know. Thanks for the help!

Ryan

 

[Duane Hookom]

You would need a table like:
tblExchangeRates
=================
FromCurrency (values like DOLLARS, EURO, YEN,...)
ToCurrency (values like DOLLARS, EURO, YEN,...)
Rate (values like 1.234, .4552,....)

 

[Marshall Barton]

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.

 

[Guest]

Marsh,
Unfortunately, this is even more confusing for me. I am not sure I am
following you guys. Sorry, but I am a newbie with Access. How is a query
going to be able to decide if the original currency equals Yen divid by this
number for Dollar and ALSO this number for Euro? This would is the situation
for the other 15 currencies. The query has to know what the original
currency is and what number to divid/multiply to give me the Dollar and Euro
equivalent. I thought IIF statements were the only way to accomplish this.
Could you give me a more simple description of what to do? Again, you guys
have been a real help. I await a response.

Thanks!
Ryan

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]

I have about 16 different currencies that I use. I need to be able to
convert each one into EURO and DOLLARS. The currencies range from Australia
Dollar to Yen. The original amounts (in original currency) are in the main
table. How can I convert each differenr currency in a query to show in a
report. It seems to me I would still have to use a long nested IIF function.
Correct? I hope I am wrong and there is some other easier way to do this.

 

[Duane Hookom]

We assume you have a currency value like 4.345 and a currency type such as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP

Marsh,
Unfortunately, this is even more confusing for me. I am not sure I am
following you guys. Sorry, but I am a newbie with Access. How is a query
going to be able to decide if the original currency equals Yen divid by
this
number for Dollar and ALSO this number for Euro? This would is the
situation
for the other 15 currencies. The query has to know what the original
currency is and what number to divid/multiply to give me the Dollar and
Euro
equivalent. I thought IIF statements were the only way to accomplish
this.
Could you give me a more simple description of what to do? Again, you
guys
have been a real help. I await a response.

Thanks!
Ryan

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]

I have about 16 different currencies that I use. I need to be able to
convert each one into EURO and DOLLARS. The currencies range from
Australia
Dollar to Yen. The original amounts (in original currency) are in the
main
table. How can I convert each differenr currency in a query to show in
a
report. It seems to me I would still have to use a long nested IIF
function.
Correct? I hope I am wrong and there is some other easier way to do
this.


I have created a report that contains a field that has string of IIF
statements (15 to be exact) related to it. This concept of this
field is the
following:

I have money in differenct currencies; the IIF statement recognizes
what
currency it is then divides it by the exchange rate; this new amount
is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements to
work,
but in the footer of my report, I want to have a sum of the 'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Guest]

Probably the most simple way is to calculate your currency in a query. I
assume you have a table for rates as well as currency types and amounts? Use
the Query builder if you are new to Access. Add both tables to your builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

We assume you have a currency value like 4.345 and a currency type such as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP

Marsh,
Unfortunately, this is even more confusing for me. I am not sure I am
following you guys. Sorry, but I am a newbie with Access. How is a query
going to be able to decide if the original currency equals Yen divid by
this
number for Dollar and ALSO this number for Euro? This would is the
situation
for the other 15 currencies. The query has to know what the original
currency is and what number to divid/multiply to give me the Dollar and
Euro
equivalent. I thought IIF statements were the only way to accomplish
this.
Could you give me a more simple description of what to do? Again, you
guys
have been a real help. I await a response.

Thanks!
Ryan

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to be able to
convert each one into EURO and DOLLARS. The currencies range from
Australia
Dollar to Yen. The original amounts (in original currency) are in the
main
table. How can I convert each differenr currency in a query to show in
a
report. It seems to me I would still have to use a long nested IIF
function.
Correct? I hope I am wrong and there is some other easier way to do
this.


I have created a report that contains a field that has string of IIF
statements (15 to be exact) related to it. This concept of this
field is the
following:

I have money in differenct currencies; the IIF statement recognizes
what
currency it is then divides it by the exchange rate; this new amount
is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements to
work,
but in the footer of my report, I want to have a sum of the 'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Guest]

The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM or
manipulate them in your reports.

Probably the most simple way is to calculate your currency in a query. I
assume you have a table for rates as well as currency types and amounts? Use
the Query builder if you are new to Access. Add both tables to your builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

We assume you have a currency value like 4.345 and a currency type such as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP

Marsh,
Unfortunately, this is even more confusing for me. I am not sure I am
following you guys. Sorry, but I am a newbie with Access. How is a query
going to be able to decide if the original currency equals Yen divid by
this
number for Dollar and ALSO this number for Euro? This would is the
situation
for the other 15 currencies. The query has to know what the original
currency is and what number to divid/multiply to give me the Dollar and
Euro
equivalent. I thought IIF statements were the only way to accomplish
this.
Could you give me a more simple description of what to do? Again, you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to be able to
convert each one into EURO and DOLLARS. The currencies range from
Australia
Dollar to Yen. The original amounts (in original currency) are in the
main
table. How can I convert each differenr currency in a query to show in
a
report. It seems to me I would still have to use a long nested IIF
function.
Correct? I hope I am wrong and there is some other easier way to do
this.


I have created a report that contains a field that has string of IIF
statements (15 to be exact) related to it. This concept of this
field is the
following:

I have money in differenct currencies; the IIF statement recognizes
what
currency it is then divides it by the exchange rate; this new amount
is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements to
work,
but in the footer of my report, I want to have a sum of the 'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Duane Hookom]

You should create a solution that normalizes your table of rates. For
instance an unnormalized table would look like (excuse the horrible wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro Japanese Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP

The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM or
manipulate them in your reports.

Probably the most simple way is to calculate your currency in a query. I
assume you have a table for rates as well as currency types and amounts?
Use
the Query builder if you are new to Access. Add both tables to your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

We assume you have a currency value like 4.345 and a currency type such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not sure I
am
following you guys. Sorry, but I am a newbie with Access. How is a
query
going to be able to decide if the original currency equals Yen divid
by
this
number for Dollar and ALSO this number for Euro? This would is the
situation
for the other 15 currencies. The query has to know what the original
currency is and what number to divid/multiply to give me the Dollar
and
Euro
equivalent. I thought IIF statements were the only way to accomplish
this.
Could you give me a more simple description of what to do? Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to be able
to
convert each one into EURO and DOLLARS. The currencies range from
Australia
Dollar to Yen. The original amounts (in original currency) are in
the
main
table. How can I convert each differenr currency in a query to
show in
a
report. It seems to me I would still have to use a long nested IIF
function.
Correct? I hope I am wrong and there is some other easier way to
do
this.


I have created a report that contains a field that has string of
IIF
statements (15 to be exact) related to it. This concept of this
field is the
following:

I have money in differenct currencies; the IIF statement
recognizes
what
currency it is then divides it by the exchange rate; this new
amount
is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements
to
work,
but in the footer of my report, I want to have a sum of the
'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF
function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Guest]

Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from my Rates
Table and the Original Amounts from my Main Table in order to produce a new
amount in either EUR or USD. I then can use this new Query for the amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts in the
New Query.

When I create this New Query, what formula should I include (and where) in
order for the query to recognize each individual Original Currency and, based
on that Original Currency, what amount to multiple/divide by in order to
display the converted amount in EUR and USD. I am under the impression the
only way this is possible is by an IIF statement because nothing else is
working for me.

The New Query needs to be able to decifer between each Currency and then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan

You should create a solution that normalizes your table of rates. For
instance an unnormalized table would look like (excuse the horrible wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro Japanese Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP

The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM or
manipulate them in your reports.

Probably the most simple way is to calculate your currency in a query. I
assume you have a table for rates as well as currency types and amounts?
Use
the Query builder if you are new to Access. Add both tables to your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency type such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not sure I
am
following you guys. Sorry, but I am a newbie with Access. How is a
query
going to be able to decide if the original currency equals Yen divid
by
this
number for Dollar and ALSO this number for Euro? This would is the
situation
for the other 15 currencies. The query has to know what the original
currency is and what number to divid/multiply to give me the Dollar
and
Euro
equivalent. I thought IIF statements were the only way to accomplish
this.
Could you give me a more simple description of what to do? Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to be able
to
convert each one into EURO and DOLLARS. The currencies range from
Australia
Dollar to Yen. The original amounts (in original currency) are in
the
main
table. How can I convert each differenr currency in a query to
show in
a
report. It seems to me I would still have to use a long nested IIF
function.
Correct? I hope I am wrong and there is some other easier way to
do
this.


I have created a report that contains a field that has string of
IIF
statements (15 to be exact) related to it. This concept of this
field is the
following:

I have money in differenct currencies; the IIF statement
recognizes
what
currency it is then divides it by the exchange rate; this new
amount
is shown
in the field title "Amount in USD" for each record on my report

I figured out the hard part of getting the string IIF statements
to
work,
but in the footer of my report, I want to have a sum of the
'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF
function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Duane Hookom]

You still don't have this setup as I suggested. I would use two currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP

Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from my
Rates
Table and the Original Amounts from my Main Table in order to produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts in the
New Query.

When I create this New Query, what formula should I include (and where) in
order for the query to recognize each individual Original Currency and,
based
on that Original Currency, what amount to multiple/divide by in order to
display the converted amount in EUR and USD. I am under the impression
the
only way this is possible is by an IIF statement because nothing else is
working for me.

The New Query needs to be able to decifer between each Currency and then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan

You should create a solution that normalizes your table of rates. For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro Japanese Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP

The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not sure
I
am
following you guys. Sorry, but I am a newbie with Access. How is
a
query
going to be able to decide if the original currency equals Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do? Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to be
able
to
convert each one into EURO and DOLLARS. The currencies range
from
Australia
Dollar to Yen. The original amounts (in original currency) are
in
the
main
table. How can I convert each differenr currency in a query to
show in
a
report. It seems to me I would still have to use a long nested
IIF
function.
Correct? I hope I am wrong and there is some other easier way
to
do
this.


I have created a report that contains a field that has string
of
IIF
statements (15 to be exact) related to it. This concept of
this
field is the
following:

I have money in differenct currencies; the IIF statement
recognizes
what
currency it is then divides it by the exchange rate; this new
amount
is shown
in the field title "Amount in USD" for each record on my
report

I figured out the hard part of getting the string IIF
statements
to
work,
but in the footer of my report, I want to have a sum of the
'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF
function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Guest]

Duane,

I created the three tables you suggested. One for the EUR conversion, USD
conversion, the EUR rates and I have included another for USD Rates since the
rates are different for each.

That was the easy part. I am still struggling in how to tie the information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

You still don't have this setup as I suggested. I would use two currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP

Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from my
Rates
Table and the Original Amounts from my Main Table in order to produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts in the
New Query.

When I create this New Query, what formula should I include (and where) in
order for the query to recognize each individual Original Currency and,
based
on that Original Currency, what amount to multiple/divide by in order to
display the converted amount in EUR and USD. I am under the impression
the
only way this is possible is by an IIF statement because nothing else is
working for me.

The New Query needs to be able to decifer between each Currency and then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan

You should create a solution that normalizes your table of rates. For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro Japanese Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not sure
I
am
following you guys. Sorry, but I am a newbie with Access. How is
a
query
going to be able to decide if the original currency equals Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do? Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to be
able
to
convert each one into EURO and DOLLARS. The currencies range
from
Australia
Dollar to Yen. The original amounts (in original currency) are
in
the
main
table. How can I convert each differenr currency in a query to
show in
a
report. It seems to me I would still have to use a long nested
IIF
function.
Correct? I hope I am wrong and there is some other easier way
to
do
this.


I have created a report that contains a field that has string
of
IIF
statements (15 to be exact) related to it. This concept of
this
field is the
following:

I have money in differenct currencies; the IIF statement
recognizes
what
currency it is then divides it by the exchange rate; this new
amount
is shown
in the field title "Amount in USD" for each record on my
report

I figured out the hard part of getting the string IIF
statements
to
work,
but in the footer of my report, I want to have a sum of the
'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF
function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are specified.

 

[Duane Hookom]

I had one conversion/rate table. I did not store a currency name as a field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR conversion, USD
conversion, the EUR rates and I have included another for USD Rates since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

You still don't have this setup as I suggested. I would use two currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP

Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from my
Rates
Table and the Original Amounts from my Main Table in order to produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts in
the
New Query.

When I create this New Query, what formula should I include (and where)
in
order for the query to recognize each individual Original Currency and,
based
on that Original Currency, what amount to multiple/divide by in order
to
display the converted amount in EUR and USD. I am under the impression
the
only way this is possible is by an IIF statement because nothing else
is
working for me.

The New Query needs to be able to decifer between each Currency and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of rates. For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a
query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a
could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not
sure
I
am
following you guys. Sorry, but I am a newbie with Access. How
is
a
query
going to be able to decide if the original currency equals Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do?
Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to
be
able
to
convert each one into EURO and DOLLARS. The currencies range
from
Australia
Dollar to Yen. The original amounts (in original currency)
are
in
the
main
table. How can I convert each differenr currency in a query
to
show in
a
report. It seems to me I would still have to use a long
nested
IIF
function.
Correct? I hope I am wrong and there is some other easier
way
to
do
this.


I have created a report that contains a field that has
string
of
IIF
statements (15 to be exact) related to it. This concept
of
this
field is the
following:

I have money in differenct currencies; the IIF statement
recognizes
what
currency it is then divides it by the exchange rate; this
new
amount
is shown
in the field title "Amount in USD" for each record on my
report

I figured out the hard part of getting the string IIF
statements
to
work,
but in the footer of my report, I want to have a sum of
the
'Total in
USD"
numbers I have created with the IIF function.

Basically, how do I sum and display the results of an IIF
function
within a
Report?


:
You should do the IIf calculation in the report's record
source query. This way the calculated field can be a
simple
=Sum(USD)

The use of umpteen nested IIf functins sounds more than a
little scary, especially if the exchange rates are included
in the expression. I suggest that you try to find a way to
use a table of exchange rates and join that table to your
data table in the query. I might be able to help with this
if you'll explain how the amount and currency are
specified.

 

[Guest]

Duane,

I have created one table as you mentioned. Sorry I misunderstood you
before. I am still stuck on the problem of actually converting my data.
Please let me know how I can do this.

Thanks!
Ryan

I had one conversion/rate table. I did not store a currency name as a field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR conversion, USD
conversion, the EUR rates and I have included another for USD Rates since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

You still don't have this setup as I suggested. I would use two currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP


Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from my
Rates
Table and the Original Amounts from my Main Table in order to produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts in
the
New Query.

When I create this New Query, what formula should I include (and where)
in
order for the query to recognize each individual Original Currency and,
based
on that Original Currency, what amount to multiple/divide by in order
to
display the converted amount in EUR and USD. I am under the impression
the
only way this is possible is by an IIF statement because nothing else
is
working for me.

The New Query needs to be able to decifer between each Currency and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of rates. For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a
query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this true?

Maybe you could provide a sample of significant fields and a
could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not
sure
I
am
following you guys. Sorry, but I am a newbie with Access. How
is
a
query
going to be able to decide if the original currency equals Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do?
Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need to
be
able
to
convert each one into EURO and DOLLARS. The currencies range
from
Australia
Dollar to Yen. The original amounts (in original currency)
are
in
the
main
table. How can I convert each differenr currency in a query
to
show in
a
report. It seems to me I would still have to use a long
nested
IIF
function.
Correct? I hope I am wrong and there is some other easier
way
to
do
this.


I have created a report that contains a field that has
string
of
IIF
statements (15 to be exact) related to it. This concept
of
this
field is the
following:

I have money in differenct currencies; the IIF statement
recognizes
what
currency it is then divides it by the exchange rate; this
new
amount
is shown
in the field title "Amount in USD" for each record on my
report

I figured out the hard part of getting the string IIF
statements
to
work,
but in the footer of my report, I want to have a sum of
the
'Total in

 

[Duane Hookom]

I don't recall you ever providing some sample records that identifies your
table structure with the relevant/significant fields.
What kind of values are you storing in your table/query and how do you want
to display them in your report?

--
Duane Hookom
MS Access MVP
--

Duane,

I have created one table as you mentioned. Sorry I misunderstood you
before. I am still stuck on the problem of actually converting my data.
Please let me know how I can do this.

Thanks!
Ryan

I had one conversion/rate table. I did not store a currency name as a
field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR conversion,
USD
conversion, the EUR rates and I have included another for USD Rates
since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

:

You still don't have this setup as I suggested. I would use two
currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP


Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two
column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from
my
Rates
Table and the Original Amounts from my Main Table in order to
produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts
in
the
New Query.

When I create this New Query, what formula should I include (and
where)
in
order for the query to recognize each individual Original Currency
and,
based
on that Original Currency, what amount to multiple/divide by in
order
to
display the converted amount in EUR and USD. I am under the
impression
the
only way this is possible is by an IIF statement because nothing
else
is
working for me.

The New Query needs to be able to decifer between each Currency and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of rates.
For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro
Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then
SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a
query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to
your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this
true?

Maybe you could provide a sample of significant fields and a
could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not
sure
I
am
following you guys. Sorry, but I am a newbie with Access.
How
is
a
query
going to be able to decide if the original currency equals
Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would
is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do?
Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a
query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need
to
be
able
to
convert each one into EURO and DOLLARS. The currencies
range
from
Australia
Dollar to Yen. The original amounts (in original
currency)
are
in
the
main
table. How can I convert each differenr currency in a
query
to
show in
a
report. It seems to me I would still have to use a long
nested
IIF
function.
Correct? I hope I am wrong and there is some other
easier
way
to
do
this.


I have created a report that contains a field that has
string
of
IIF
statements (15 to be exact) related to it. This
concept
of
this
field is the
following:

I have money in differenct currencies; the IIF
statement
recognizes
what
currency it is then divides it by the exchange rate;
this
new
amount
is shown
in the field title "Amount in USD" for each record on
my
report

I figured out the hard part of getting the string IIF
statements
to
work,
but in the footer of my report, I want to have a sum of
the
'Total in

 

[Guest]

I have 600 records that include the following data for Bank Guarantees:

Country - Name - Bank Name - Currency - Amount - Expiration Date - Type

I want to be able to show the EUR and USD equivalent for each guarantee,
while also keeping all the above information (including the 'Original
Amount'). I suppose I should show this in some sort of Query so that
(getting back to my main question a while ago) I can then show the sum of the
EUR and USD equivalent for all guarantees in a report.

I am not sure how to go about doing this in a Query or any other option
without the use of a long IIF statement. I need the Query to decifer what
currency each guarantee is based on and then apply the appropriate rate.

I hope this explains my problem. If not let me know.

Thanks,
Ryan

I don't recall you ever providing some sample records that identifies your
table structure with the relevant/significant fields.
What kind of values are you storing in your table/query and how do you want
to display them in your report?

--
Duane Hookom
MS Access MVP
--

Duane,

I have created one table as you mentioned. Sorry I misunderstood you
before. I am still stuck on the problem of actually converting my data.
Please let me know how I can do this.

Thanks!
Ryan

I had one conversion/rate table. I did not store a currency name as a
field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR conversion,
USD
conversion, the EUR rates and I have included another for USD Rates
since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

:

You still don't have this setup as I suggested. I would use two
currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP


Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two
column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from
my
Rates
Table and the Original Amounts from my Main Table in order to
produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts
in
the
New Query.

When I create this New Query, what formula should I include (and
where)
in
order for the query to recognize each individual Original Currency
and,
based
on that Original Currency, what amount to multiple/divide by in
order
to
display the converted amount in EUR and USD. I am under the
impression
the
only way this is possible is by an IIF statement because nothing
else
is
working for me.

The New Query needs to be able to decifer between each Currency and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of rates.
For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro
Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then
SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a
query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to
your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this
true?

Maybe you could provide a sample of significant fields and a
could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not
sure
I
am
following you guys. Sorry, but I am a newbie with Access.
How
is
a
query
going to be able to decide if the original currency equals
Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would
is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do?
Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a
query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,
T.amount * X.rate As USDamount
FROM thetable As T LEFT JOIN tblExchangeRates As X
ON T.cuurency = X.FromCurrency
WHERE X.ToCurrency ="Dollars"

No IIfs at all and it'll be a lot faster and (hopefully)
easier to understand.
--
Marsh
MVP [MS Access]


Ryan G wrote:
I have about 16 different currencies that I use. I need
to
be
able
to
convert each one into EURO and DOLLARS. The currencies
range
from
Australia
Dollar to Yen. The original amounts (in original

 

[Guest]

Duane,

I figured out how to view the Converted Data in a report for each guarantee
(record). Now I have come to the same original problem: How can I sum a
Calculated Field (Amount * Rate) in a Report to show a total number?

Ryan

I have 600 records that include the following data for Bank Guarantees:

Country - Name - Bank Name - Currency - Amount - Expiration Date - Type

I want to be able to show the EUR and USD equivalent for each guarantee,
while also keeping all the above information (including the 'Original
Amount'). I suppose I should show this in some sort of Query so that
(getting back to my main question a while ago) I can then show the sum of the
EUR and USD equivalent for all guarantees in a report.

I am not sure how to go about doing this in a Query or any other option
without the use of a long IIF statement. I need the Query to decifer what
currency each guarantee is based on and then apply the appropriate rate.

I hope this explains my problem. If not let me know.

Thanks,
Ryan

I don't recall you ever providing some sample records that identifies your
table structure with the relevant/significant fields.
What kind of values are you storing in your table/query and how do you want
to display them in your report?

--
Duane Hookom
MS Access MVP
--

Duane,

I have created one table as you mentioned. Sorry I misunderstood you
before. I am still stuck on the problem of actually converting my data.
Please let me know how I can do this.

Thanks!
Ryan

:

I had one conversion/rate table. I did not store a currency name as a
field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR conversion,
USD
conversion, the EUR rates and I have included another for USD Rates
since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

:

You still don't have this setup as I suggested. I would use two
currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP


Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including two
column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates from
my
Rates
Table and the Original Amounts from my Main Table in order to
produce a
new
amount in either EUR or USD. I then can use this new Query for the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD amounts
in
the
New Query.

When I create this New Query, what formula should I include (and
where)
in
order for the query to recognize each individual Original Currency
and,
based
on that Original Currency, what amount to multiple/divide by in
order
to
display the converted amount in EUR and USD. I am under the
impression
the
only way this is possible is by an IIF statement because nothing
else
is
working for me.

The New Query needs to be able to decifer between each Currency and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of rates.
For
instance an unnormalized table would look like (excuse the horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro
Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416 0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency Amount],0)
Of course you would need to do this for all currencies and then
SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency in a
query.
I
assume you have a table for rates as well as currency types and
amounts?
Use
the Query builder if you are new to Access. Add both tables to
your
builder
and link them on the proper fields. In the top line of the Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is this
true?

Maybe you could provide a sample of significant fields and a
could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


Marsh,
Unfortunately, this is even more confusing for me. I am not
sure
I
am
following you guys. Sorry, but I am a newbie with Access.
How
is
a
query
going to be able to decide if the original currency equals
Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This would
is
the
situation
for the other 15 currencies. The query has to know what the
original
currency is and what number to divid/multiply to give me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way to
accomplish
this.
Could you give me a more simple description of what to do?
Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a
query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,

 

[Duane Hookom]

Try set a control source of a text box to:
=Sum(Amount * Rate)

--
Duane Hookom
MS Access MVP
--

Duane,

I figured out how to view the Converted Data in a report for each
guarantee
(record). Now I have come to the same original problem: How can I sum a
Calculated Field (Amount * Rate) in a Report to show a total number?

Ryan

I have 600 records that include the following data for Bank Guarantees:

Country - Name - Bank Name - Currency - Amount - Expiration Date - Type

I want to be able to show the EUR and USD equivalent for each guarantee,
while also keeping all the above information (including the 'Original
Amount'). I suppose I should show this in some sort of Query so that
(getting back to my main question a while ago) I can then show the sum of
the
EUR and USD equivalent for all guarantees in a report.

I am not sure how to go about doing this in a Query or any other option
without the use of a long IIF statement. I need the Query to decifer
what
currency each guarantee is based on and then apply the appropriate rate.

I hope this explains my problem. If not let me know.

Thanks,
Ryan

I don't recall you ever providing some sample records that identifies
your
table structure with the relevant/significant fields.
What kind of values are you storing in your table/query and how do you
want
to display them in your report?

--
Duane Hookom
MS Access MVP
--

Duane,

I have created one table as you mentioned. Sorry I misunderstood you
before. I am still stuck on the problem of actually converting my
data.
Please let me know how I can do this.

Thanks!
Ryan

:

I had one conversion/rate table. I did not store a currency name as
a
field
or table name since that would not be good practice.

--
Duane Hookom
MS Access MVP
--

Duane,

I created the three tables you suggested. One for the EUR
conversion,
USD
conversion, the EUR rates and I have included another for USD
Rates
since
the
rates are different for each.

That was the easy part. I am still struggling in how to tie the
information
together to show my results in a query for each of my 600 records.

What are the next steps?

Thanks!
Ryan

:

You still don't have this setup as I suggested. I would use two
currency
type fields and one rate field.

--
Duane Hookom
MS Access MVP


Ok ... I have a table set up with the following parameters:

Currency - EUR Rate - USD Rate
AUD XXXXX XXXXX
USD XXXXX XXXXX
JPY XXXXX XXXXX
EUR XXXXX XXXXX
USD XXXXX XXXXX
GBP XXXXX XXXXX
(the "XXXXX" are actual numbers)

I then have a Main table that contains all my data including
two
column
headings that are blank:
Amount in EUR
Amount in USD
(I have about 600 different records in this Main Table.)

I understand why I need to build a Query to include the rates
from
my
Rates
Table and the Original Amounts from my Main Table in order to
produce a
new
amount in either EUR or USD. I then can use this new Query for
the
amounts I
include with my Reports.

The problem is I can't figure out how to show these EUR/USD
amounts
in
the
New Query.

When I create this New Query, what formula should I include
(and
where)
in
order for the query to recognize each individual Original
Currency
and,
based
on that Original Currency, what amount to multiple/divide by in
order
to
display the converted amount in EUR and USD. I am under the
impression
the
only way this is possible is by an IIF statement because
nothing
else
is
working for me.

The New Query needs to be able to decifer between each Currency
and
then
apply the appropriate rate.

Once again, I really appreciate everyone's help.

Ryan



:

You should create a solution that normalizes your table of
rates.
For
instance an unnormalized table would look like (excuse the
horrible
wrapping
and format but you should be able to understand the concept):
===un-normalized====
Currency names United Kingdom Pound Canadian Dollar Euro
Japanese
Yen
Swiss Franc US Dollar
United Kingdom Pound 1 0.4602 0.6855 0.005036 0.4416
0.55093
Canadian Dollar 2.1709 1 1.4882 0.010935 0.9587 1.19639
Euro 1.4582 0.6712 1 0.007346 0.6445 0.80361
Japanese Yen 198.47 91.3465 136.06 1 87.6282 109.36
Swiss Franc 2.2631 1.042 1.5512 0.011402 1 1.2475
US Dollar 1.8145 0.8353 1.2439 0.00914 0.8013 1

===Same data normalized====
CurrencyFrom CurrencyTo Rate
Canadian Dollar Canadian Dollar $1.00
Canadian Dollar Euro $0.67
Canadian Dollar Japanese Yen $91.35
Canadian DollarS wiss Franc $1.04
Canadian Dollar United Kingdom Pound $0.46
Canadian Dollar US Dollar $0.84
Euro Canadian Dollar $1.49
Euro Euro $1.00
Euro Japanese Yen $136.06
Euro Swiss Franc $1.55
Euro United Kingdom Pound $0.69
Euro US Dollar $1.24
Japanese Yen Canadian Dollar $0.01
Japanese Yen Euro $0.01
Japanese Yen Japanese Yen $1.00
Japanese Yen Swiss Franc $0.01
Japanese Yen United Kingdom Pound $0.01
Japanese Yen US Dollar $0.01
Swiss Franc Canadian Dollar $0.96
Swiss Franc Euro $0.64
Swiss Franc Japanese Yen $87.63
Swiss Franc Swiss Franc $1.00
Swiss Franc United Kingdom Pound $0.44
Swiss Franc US Dollar $0.80
United Kingdom Pound Canadian Dollar $2.17
United Kingdom Pound Euro $1.46
United Kingdom Pound Japanese Yen $198.47
United Kingdom Pound Swiss Franc $2.26
United Kingdom Pound United Kingdom Pound $1.00
United Kingdom Pound US Dollar $1.81
US Dollar Canadian Dollar $1.20
US Dollar Euro $0.80
US Dollar Japanese Yen $109.36
US Dollar Swiss Franc $1.25
US Dollar United Kingdom Pound $0.55
US Dollar US Dollar $1.00

--
Duane Hookom
MS Access MVP


The IIF column would look like this: FieldWithYenAmount:
IIf([FieldCurrencyInetifier]="YEN",[FieldWithCurrency
Amount],0)
Of course you would need to do this for all currencies and
then
SUM
or
manipulate them in your reports.

:

Probably the most simple way is to calculate your currency
in a
query.
I
assume you have a table for rates as well as currency types
and
amounts?
Use
the Query builder if you are new to Access. Add both tables
to
your
builder
and link them on the proper fields. In the top line of the
Query
Builder,type. YenValue:= [FieldWithYenAmount]
Operator[FieldWithExchangeRate]

:

We assume you have a currency value like 4.345 and a
currency
type
such
as
"YEN" or "DOLLARS" or "FRANC" in fields in a table. Is
this
true?

Maybe you could provide a sample of significant fields
and a
could
records
as well as how you want something to display in a report?

--
Duane Hookom
MS Access MVP


message
Marsh,
Unfortunately, this is even more confusing for me. I
am not
sure
I
am
following you guys. Sorry, but I am a newbie with
Access.
How
is
a
query
going to be able to decide if the original currency
equals
Yen
divid
by
this
number for Dollar and ALSO this number for Euro? This
would
is
the
situation
for the other 15 currencies. The query has to know
what the
original
currency is and what number to divid/multiply to give
me the
Dollar
and
Euro
equivalent. I thought IIF statements were the only way
to
accomplish
this.
Could you give me a more simple description of what to
do?
Again,
you
guys
have been a real help. I await a response.

Thanks!
Ryan

:

Building on Duane's suggested table, you can the use a
query
such as:

SELECT T.flda, T.fldb, . . . , T.amount, T.currency,