Efficient database construction help

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

re: Efficient database construction help

On Sun, 9 Jan 2005 16:11:05 +0000 (UTC), "Tony Williams"
<tw@tcpinvalid.com> wrote:

A Currency field holds a monetary value, but says NOTHING about the
eh.. currency. Let's not try to make it more than it is.

I think the best solution is to store the ReportingCurrency (text)
field in the Company table. The statistics table (only 1 table is
needed) obviously has a CompanyID field, and through a join you can
look up how to interpret the monetary values. You can use the same
field in your WHERE clause if you only want Euro data.

Currency conversion formulas are available on the net, but really
don't require more than grade school math. The conversion function can
be called from a query.

-Tom.

[color=blue]
>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
>[/color]

re: Efficient database construction help

Tony,
This would give you a company dimension, an attribute of which would be
currency type. Since currency rates change constantly it makes sense to
build a currency exchange rate table that tracks exchange rates by unit of
time--hour, day, whatever is needed. Which means time becomes a dimension
as well in this schema. Your measured facts are the statistics you have to
report. I don't post here to market myself. These days I am a cook. I
used to be a DBA back when everybody that could type was working as a
programer. But if you want a further explanation of dimensions, facts,
attributes, etc. I can repeat my past summary of data warehouse ideas. You
can also check out http://www.ralphkimball.com for more stuff on data
warehouses.

"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:crrl2p$sle$1@hercules.btinternet.com...[color=blue]
>I have two groups of companies each producing the same statistics . . .[/color]

re: Efficient database construction help

Thanks Alan. That site you recommended was a really useful resource and
thanks for your comments.
Incidentally I am retired and also a "cook", well at least I enjoy cooking
most of the meals my wife and I have together (she still works!).
Tony
"Alan Webb" <knogeek@hotmail.com> wrote in message
news:d7SdnVoyLecGG3zcRVn-jQ@comcast.com...[color=blue]
> Tony,
> This would give you a company dimension, an attribute of which would be
> currency type. Since currency rates change constantly it makes sense to
> build a currency exchange rate table that tracks exchange rates by unit of
> time--hour, day, whatever is needed. Which means time becomes a dimension
> as well in this schema. Your measured facts are the statistics you have[/color]
to[color=blue]
> report. I don't post here to market myself. These days I am a cook. I
> used to be a DBA back when everybody that could type was working as a
> programer. But if you want a further explanation of dimensions, facts,
> attributes, etc. I can repeat my past summary of data warehouse ideas.[/color]
You[color=blue]
> can also check out http://www.ralphkimball.com for more stuff on data
> warehouses.
>
> "Tony Williams" <tw@tcpinvalid.com> wrote in message
> news:crrl2p$sle$1@hercules.btinternet.com...[color=green]
> >I have two groups of companies each producing the same statistics . . .[/color]
>
>[/color]

re: Efficient database construction help

Thanks Tom I think I was coming to that conclusion as well, good to have it
confirmed by the experts!!
Tony
"Tom van Stiphout" <no.spam.tom7744@cox.net> wrote in message
news:c2v2u0l43mhq5pnjdbr0onv3cp64sj388s@4ax.com...[color=blue]
> On Sun, 9 Jan 2005 16:11:05 +0000 (UTC), "Tony Williams"
> <tw@tcpinvalid.com> wrote:
>
> A Currency field holds a monetary value, but says NOTHING about the
> eh.. currency. Let's not try to make it more than it is.
>
> I think the best solution is to store the ReportingCurrency (text)
> field in the Company table. The statistics table (only 1 table is
> needed) obviously has a CompanyID field, and through a join you can
> look up how to interpret the monetary values. You can use the same
> field in your WHERE clause if you only want Euro data.
>
> Currency conversion formulas are available on the net, but really
> don't require more than grade school math. The conversion function can
> be called from a query.
>
> -Tom.
>
>[color=green]
> >I have two groups of companies each producing the same statistics,[/color][/color]
however[color=blue][color=green]
> >one group produces them in £ sterling and the other group (much smaller[/color][/color]
only[color=blue][color=green]
> >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[/color][/color]
same[color=blue][color=green]
> >fields and link the tables on a common field or add duplicate fields,[/color][/color]
with a[color=blue][color=green]
> >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[/color][/color]
the[color=blue][color=green]
> >joint report, eg I know that I'm going to have to convert all the figures[/color][/color]
to[color=blue][color=green]
> >Sterling to produce the combined report.
> >TIA
> >Tony Williams
> >[/color]
>[/color]