Efficient database construction help

[Tony Williams]

I have two groups of companies each producing the same statistics, however
one group produces them in £ sterling and the other group (much smaller only
5 companies) produces them in Euros. They send in the information in the
same format but in different currencies. I need to produce reports of ALL
the companies, with the euro figures converted to sterling, and also a
separate report for the Euro companies in Euros. Which would be the most
efficient way to construct the database, two separate tables with the same
fields and link the tables on a common field or add duplicate fields, with a
euro identifier in the name, to the sterling table which I have already
built? If I use two separate tables are there any pitfalls in creating the
joint report, eg I know that I'm going to have to convert all the figures to
Sterling to produce the combined report.
TIA
Tony Williams

 

[Joseph Meehan]

I have two groups of companies each producing the same statistics,
however one group produces them in £ sterling and the other group
(much smaller only 5 companies) produces them in Euros. They send in
the information in the same format but in different currencies. I
need to produce reports of ALL the companies, with the euro figures
converted to sterling, and also a separate report for the Euro
companies in Euros. Which would be the most efficient way to
construct the database, two separate tables with the same fields and
link the tables on a common field or add duplicate fields, with a
euro identifier in the name, to the sterling table which I have
already built? If I use two separate tables are there any pitfalls in
creating the joint report, eg I know that I'm going to have to
convert all the figures to Sterling to produce the combined report.
TIA
Tony Williams

Is the £ fixed against the ? or does it float? If it floats I suggest a
separate filed for ? and maybe an additional field for date converted along
with the converted value in the £ field. I am sure someone is going to
want to go back and will argue about the conversion rate.

I like the single table idea myself.

 

[Tony Williams]

The rate changes almost daily but we take a monthly Bank of England estimate
Tony

 

[Joseph Meehan]

The rate changes almost daily but we take a monthly Bank of England
estimate Tony

That's what I thought, but I have not been over there for a while and IE
is Euro.

 

[Albert D. Kallal]

I would add a field to the record that tells you what kind of currency the
currency value field is.

That way, you have a design that tomorrow you could add USA dollars, or
Canadian dollars and not have to re-write the design.

So, each record you add will have a value amount field, and then 2nd field
that tells you what kind of currency this is.

Thus, you can expand, and receive data from 1, 2, or 5 different currencies.

Further, your "currency" table could contain more then just the name (Euro,
Stealing, USA, etc), you could expand on this table and add dates, and
conversation rates that would allow you to generate reports.

 

[Tony Williams]

Thanks Albert That makes a whole lot of sense
Tony