Currency Conversion problem

[Tony Williams]

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that holds the
name of the company and a date field to identify the month to which the data
applies
Table2 holds numeric data fields in Euros, a text field that holds the name
of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date fields
in Table1 and Table2 and a numeric filed that holds the value of the Euro in
that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about this?
As ever thanks for being there!
Tony

 

[Guest]

wouldn't it be easier to have all the things you want in the same table
[date] [company] [sterling1] [sterling2] [euro1] [euro2] [eurovalue]

and just make [euro1] = [sterling1] * [eurovalue]
and [euro2] = [sterling2] * [eurovalue]

 

[Tony Williams]

Thanks for that. I had thought of that but the sterling table already holds
some 40 numeric fields and it would mean almost doubling that to 80 so I
thought a separate table would be more efficient. What do you think?
Tony

wouldn't it be easier to have all the things you want in the same table
[date] [company] [sterling1] [sterling2] [euro1] [euro2] [eurovalue]

and just make [euro1] = [sterling1] * [eurovalue]
and [euro2] = [sterling2] * [eurovalue]

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that holds the
name of the company and a date field to identify the month to which the data
applies
Table2 holds numeric data fields in Euros, a text field that holds the name
of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date fields
in Table1 and Table2 and a numeric filed that holds the value of the Euro in
that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about this?
As ever thanks for being there!
Tony

 

[Nikos Yannacopoulos]

I'm afraid I'll disagree all the way with Axeman, whose proposal defies
the whole idea of relational databases: data normalization.

My suggestion here is: don't update, actually don't double store at all!
You don't need to store amounts in both currencies, just calculate the
conversion on the fly whenever needed. Also, stick with one table, the
two will only get you into trouble. Just add a field for currency so you
can tell if amounts in each record are in GBP or EUR. Then in a query
join the main table with the exchange rates table on the month field,
and use a caclulated field like:

GBPAmount: [Data1] * IIf([Currency] = "GBP", 1, [Eurovalue])

You can then use this for your reporting.

HTH,
Nikos

 

[Guest]

if you mean 40 lots of the data1 data2 ones then all on 1 table would be
over 80 ouch
do you actually need the euro value in a table or would just in a report be
ok?
if you just want to display the euro data in a report with the other data
then you only have to add a eurovalue column to the 1 table with the sterling
values and in the report add a text box with the control source
=[data1]*[eurovalue]
or am I way off the mark

Thanks for that. I had thought of that but the sterling table already holds
some 40 numeric fields and it would mean almost doubling that to 80 so I
thought a separate table would be more efficient. What do you think?
Tony

wouldn't it be easier to have all the things you want in the same table
[date] [company] [sterling1] [sterling2] [euro1] [euro2] [eurovalue]

and just make [euro1] = [sterling1] * [eurovalue]
and [euro2] = [sterling2] * [eurovalue]

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that holds the
name of the company and a date field to identify the month to which the data
applies
Table2 holds numeric data fields in Euros, a text field that holds the name
of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date fields
in Table1 and Table2 and a numeric filed that holds the value of the Euro in
that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about this?
As ever thanks for being there!
Tony

 

[Tony Williams]

Thanks Nikos I had thought that keeping the tables separate was a better
idea. While I agree with your suggestion my Table1 already holds data for
other companies in £sterling and a number of reports I have are based on
that table. There are a considerable number of calculated control;s in those
reports and I thought it was going to get too complicated if I did the
conversion on the fly. I thought it would be easier to update my Table1 with
sterling values for the Euro companies and then I don't have to change all
my reports.
Is that any clearer?
Thanks
Tony

I'm afraid I'll disagree all the way with Axeman, whose proposal defies
the whole idea of relational databases: data normalization.

My suggestion here is: don't update, actually don't double store at all!
You don't need to store amounts in both currencies, just calculate the
conversion on the fly whenever needed. Also, stick with one table, the
two will only get you into trouble. Just add a field for currency so you
can tell if amounts in each record are in GBP or EUR. Then in a query
join the main table with the exchange rates table on the month field,
and use a caclulated field like:

GBPAmount: [Data1] * IIf([Currency] = "GBP", 1, [Eurovalue])

You can then use this for your reporting.

HTH,
Nikos

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that holds the
name of the company and a date field to identify the month to which the data
applies
Table2 holds numeric data fields in Euros, a text field that holds the name
of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date fields
in Table1 and Table2 and a numeric filed that holds the value of the Euro in
that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about this?
As ever thanks for being there!
Tony

 

[Nikos Yannacopoulos]

Tony,

Yes, it's clear; it is also too common a mistake. My advice is, next
time you start a project, take all the time required to make sure you
have a well normalized, robust data design before you move any further,
and I assure you it will pay in the rest of your development. Poor data
design can easily result in a nightmare.

Nikos

Thanks Nikos I had thought that keeping the tables separate was a better
idea. While I agree with your suggestion my Table1 already holds data for
other companies in £sterling and a number of reports I have are based on
that table. There are a considerable number of calculated control;s in those
reports and I thought it was going to get too complicated if I did the
conversion on the fly. I thought it would be easier to update my Table1 with
sterling values for the Euro companies and then I don't have to change all
my reports.
Is that any clearer?
Thanks
Tony

I'm afraid I'll disagree all the way with Axeman, whose proposal defies
the whole idea of relational databases: data normalization.

My suggestion here is: don't update, actually don't double store at all!
You don't need to store amounts in both currencies, just calculate the
conversion on the fly whenever needed. Also, stick with one table, the
two will only get you into trouble. Just add a field for currency so you
can tell if amounts in each record are in GBP or EUR. Then in a query
join the main table with the exchange rates table on the month field,
and use a caclulated field like:

GBPAmount: [Data1] * IIf([Currency] = "GBP", 1, [Eurovalue])

You can then use this for your reporting.

HTH,
Nikos

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that holds

the

name of the company and a date field to identify the month to which the

data

applies
Table2 holds numeric data fields in Euros, a text field that holds the

name

of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date

fields

in Table1 and Table2 and a numeric filed that holds the value of the

Euro in

that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about

this?

As ever thanks for being there!
Tony

 

[Tony Williams]

Thanks Nikos very sound advice. However I'm stuck with this database as it
stands. There are only 6 companies that provide data in Euros and 34 that
provide data in Sterling. The sterling database was already set up and I'm
trying to accommodate the 6 Euro companies. The problem is they supply all
the same data but it is in Euros that's why I thought of duplicating the
current database and input the Euro companies' data in the new database then
updating the Sterling database with the 6 Euro companies' data, I don't
really want to rewrite all the reports of which there are many! Any ideas?
Thank you
Tony

Tony,

Yes, it's clear; it is also too common a mistake. My advice is, next
time you start a project, take all the time required to make sure you
have a well normalized, robust data design before you move any further,
and I assure you it will pay in the rest of your development. Poor data
design can easily result in a nightmare.

Nikos

Thanks Nikos I had thought that keeping the tables separate was a better
idea. While I agree with your suggestion my Table1 already holds data for
other companies in £sterling and a number of reports I have are based on
that table. There are a considerable number of calculated control;s in those
reports and I thought it was going to get too complicated if I did the
conversion on the fly. I thought it would be easier to update my Table1 with
sterling values for the Euro companies and then I don't have to change all
my reports.
Is that any clearer?
Thanks
Tony

I'm afraid I'll disagree all the way with Axeman, whose proposal defies
the whole idea of relational databases: data normalization.

My suggestion here is: don't update, actually don't double store at all!
You don't need to store amounts in both currencies, just calculate the
conversion on the fly whenever needed. Also, stick with one table, the
two will only get you into trouble. Just add a field for currency so you
can tell if amounts in each record are in GBP or EUR. Then in a query
join the main table with the exchange rates table on the month field,
and use a caclulated field like:

GBPAmount: [Data1] * IIf([Currency] = "GBP", 1, [Eurovalue])

You can then use this for your reporting.

HTH,
Nikos

Tony Williams wrote:

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that holds
the

name of the company and a date field to identify the month to which the
data

applies
Table2 holds numeric data fields in Euros, a text field that holds the
name

of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date
fields

in Table1 and Table2 and a numeric filed that holds the value of the

Euro in

that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about
this?

As ever thanks for being there!
Tony

 

[Tony Williams]

Hi axeman!
There are only 6 companies that provide data in Euros and 34 that already
provide data in Sterling. The sterling database was already set up and I'm
trying to accommodate the 6 Euro companies. The problem is they supply all
the same data but it is in Euros that's why I thought of duplicating the
current database and input the Euro companies' data in the new database then
updating the Sterling database with the 6 Euro companies' data, I don't
really want to rewrite all the reports of which there are many! Any ideas?
Tony

if you mean 40 lots of the data1 data2 ones then all on 1 table would be
over 80 ouch
do you actually need the euro value in a table or would just in a report be
ok?
if you just want to display the euro data in a report with the other data
then you only have to add a eurovalue column to the 1 table with the sterling
values and in the report add a text box with the control source
=[data1]*[eurovalue]
or am I way off the mark

Thanks for that. I had thought of that but the sterling table already holds
some 40 numeric fields and it would mean almost doubling that to 80 so I
thought a separate table would be more efficient. What do you think?
Tony

wouldn't it be easier to have all the things you want in the same table
[date] [company] [sterling1] [sterling2] [euro1] [euro2] [eurovalue]

and just make [euro1] = [sterling1] * [eurovalue]
and [euro2] = [sterling2] * [eurovalue]

:

I have three tables Table1, Table2, and Table3
Table1 holds numeric data fields in £sterling, a text field that

holds
the

name of the company and a date field to identify the month to which

the
data

applies
Table2 holds numeric data fields in Euros, a text field that holds

the
name

of the company and a date field to identify the month to which the data
applies
Table3 holds two fields; a date field which coincides with the date fields
in Table1 and Table2 and a numeric filed that holds the value of the Euro in
that month
The fields in Table1 and Table2 have the same names.
For any month I want to convert the data for each company in Table2 from
Euros to £sterling and update the equivalent field in Table1
Table2 data is
Date Company Data1 Data2
01/01/05 CompanyA 2200 3400
01/01/05 CompanyB 1500 1650

01/02/05 CompanyA 2000 3000
01/02/05 CompanyB 1500 1550

Table3 data is
Date Eurovalue
01/01/05 1.6
01/02/05 1.5

I want to update the Data1 and Data2 fields in Table1 with the converted
values of Data1 and Data2 from Table2
Something like [Table1].[Data1] for [CompanyA] on 01/01/05 =
[Table2].[Data1] for [CompanyA] on 01/01/05 multiplied by [Eurovalue] on
01/01/05 ( which is 1.6) and so on for all companies
Can someone start me off in the right direction as to how I go about this?
As ever thanks for being there!
Tony