Select from an other table and ........ 1

[straatlang]

Hi,

I want to extend the query and join some tables to be able to have a report with the name of each account and the exchange rate of the currency. And finally I want to see the total value in EUR in a seperate column.

This is table movements.

Value Date Account Amount Cur DRCR
1-1-2003 123 2000 EUR CR
1-2-2003 456 1500 EUR CR
1-2-2003 123 1450 USD CR
1-4-2003 456 1250 EUR CR
1-7-2003 123 800 USD CR
15-1-2003 123 950 EUR CR
20-1-2003 789 5000 USD CR
27-1-2003 456 5000 USD CR
28-1-2003 456 3000 USD CR
29-1-2003 123 10000 EUR DT


SELECT Movements.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, Movements.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate
FROM Movements
WHERE (((Movements.[Value Date])<=DateValue([Enter Value Date])))
GROUP BY Movements.Account, Movements.Cur;

Table :names

Account Name
123 ABC
456 DEF
789 GHJ

Table : rates

CurID Erate
EUR 1
USD 0.98
GBP 0.34


meaning 1 EUR = 0.98 USD

This the result I have when running the query. Thanks to R937

Account Balance Cur FLAG SelectedDate
123 7050 EUR DR 31-12-2003
123 2250 USD CR 31-12-2003
456 2750 EUR CR 31-12-2003
456 8000 USD CR 31-12-2003
789 5000 USD CR 31-12-2003

Now the name of each account should be added and the exchange rate and the colomn where I can see the value also in EUR.

Account Balance Cur FLAG SelectedDate Name Erate V.EUR
123 7050 EUR DR 31-12-2003 ABC 1 7050
123 2250 USD CR 31-12-2003 ABC 0.98 2205
456 2750 EUR CR 31-12-2003 DEF 1 2750
456 8000 USD CR 31-12-2003 DEF 0.98 7840
789 5000 USD CR 31-12-2003 GHJ 0.98 4900


I hope that someone can help me.

regards

Straatlang

 

[Trendsetter]

You need to add the table that holds the account name and the table of exchange rates. Then drag the required fields onto your report.
You need to identify which exchange rate is applicable to each entry and set up a calculated field that will take the balance and multiply by the appropriate exchange rate.

If you cannot figure it out sned me the app to frank@fhsservices.co.uk and I will set it up for you!

 

[straatlang]

Dear Trendsetter,

I will send you more information.

Thanks

Regards

 

[straatlang]

I have tried your suggestion and it worked

SELECT Movements.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, Movements.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, Rates.Erate, Names.Name, [Erate]*[Balance] AS V_EURO
FROM (Movements INNER JOIN [Names] ON Movements.Account = Names.Account) INNER JOIN Rates ON Movements.Cur = Rates.CurID
WHERE (((Movements.[Value Date])<=DateValue([Enter Value Date])))
GROUP BY Movements.Account, Movements.Cur, Rates.Erate, Names.Name;

But what if the table has more rate per currency
and I want to get the rate that <= to the value date.

CurID Erate RateDate
EUR 1 01-01-2003
USD 0.98 01-01-2003
GBP 0.36 02-01-2003
USD 0.99 02-01-2003
GBP 0.36 03-01-2003
USD 0.99 03-01-2003
GBP 0.34 03-01-2003


How should I achive this.

Any suggestions

Regards
Straatlang