Rejoining queries in one query

[Ssstacey]

Hi,

I have a query in which I have rates a period of time for several companies. However, these rates can be in different currencies depending on the company. So, I created a a couple ( the number of queries depends on the amount of different currencies) of queries to convert all the rates in one common currency.
What I want to do now is put all the rates of all the company back in one query. Everytime I end up with an empty query... What am I doing wrong?? ( and it's not that the other queries are empty.. I check that already..)
Any ideas?
Stacey

 

[Len Boorman]

Not sure why you are getting the result you are but would like to suggest and alternative approach.

Set up a table with all the different currencies with a conversion to the single currency you want. User this table within your queries so that you dynamically convert and analyse on this basis. No reason then to amend any data and original data stays in original form,

len

 

[Ssstacey]

Hi LEn,

what u mean is that I do not split up the query in the first place?
In the original query I have (among other things) the following fields:
COMPANY | YEAR | MONTH | RATES | CURRENCY

Only for the companies with a specific currency I want to convert.
But the problem is that there are more than one currency..So I have company with rates in pound and other in yen... If I select the query on yen, I cannot select on pund too.
How can I 'tell' the query to convert with the rates for pound when it's pound, yen when its yen.. etc... and still keep everything in one query?
Stacey

 

[Len Boorman]

The conversion table has 2 fields
Currency and Factor

Currency field contains Yen,Pund,Euro,US$ etc and the Factor field contains a number to convert said currency into say Sterling

Your original query has a link from Currency Field to Currency field in Currency table.

In your query you then have Rates and a calculated field say Sterling that is Rates*Factor

This will convert on the run all currencies to your chosen currency without altering any of the original data

L

 

[Ssstacey]

Thanx,
But what if the factor (the exchange rate) is changes every month? How do I do it then?
Stacey

 

[Len Boorman]

Basically it would be nice to have a set exchange rate for say a year, then at least you would be able to do real comparisons between monthly results, but failing that all you need to do is change the factor in the Currency Table.

Now in case you say but I need to know what is was last month then you could include a Month field in the Currency table and link your original query to that as well.

That will make it a bit of a dogs dinner and agreeing a budgerary exchange rate for a period is much cleaner

L

 

[Ssstacey]

hi L,
Sorry, I did not explain myself very clearly..
What I meant is that i have a series of exchange rates for every month for over a long period of time.. So, it's a whole series of factors that I have and not only one constant factor. What I want to do is multiply the rate of a specific month with the concerning xchange rate of that month... I think that changes the situation a bit, right?
Stacey

 

[Len Boorman]

Currency Table

Currency Year Month Factor
Yen 2005 Jan xxxxxxxxx
US$ 2005 Jan xxxxxx
Yen 2005 Feb xxxxxx

Your basic data query retrieves

Customer Rate Month Currency.

Join Currency to tbl_Currency.Currency and Month to tbl_Currency.Month

Converted rate is new calculated field in your query and is Rate*Factor

The currency and Month join ensure that correct factor is used

Len

 

[Ssstacey]

Sorry for the late reaction... (got different time zone over here.. )
Thank u for the tips. I did change the structure a bit, and now its working properly..
Stacey