# help needed for exchange rate query for paypal

I have been using cacl to do the accounts for my business for several years, i am now moving to Base 5.1.
I have a table for paypal transactions and I need to be able to calculate the exchange rate between different currencies.
here is the table

Date | Heure | Type | Devise |Avant commission

28/07/17|10.03 |conversion | USD | -536.25
28/07/17|10.03 |conversion | EUR | 410.25

29/07/17|21.45 |payment | USD | 75.99

30/0/7/17|08.00|Bank trans| EUR | -400.00

And so on, there can be up to 100 lines, and I would need to filter out all but “conversion”.

The columns date, Heure, Type, Dévise, and Avant commission are needed for the query, on one line is the amount to be exchanged and its currency(devise) and the next line is the amount it equals in the home currency, I then divide one by the other to get the exchange rate(in calc I use a formula in the table)=IF(\$I1155=“USD”,IF(OR(G1155=“Conversion de devise initiée par l’utilisateur”),\$L1156/\$L1155*-1,\$T1156+0),T1156+0)
this formula gives me the exchange rate for USD against EUR. I have the same for GBP, I could use an external calc sheet but would prefer to keep all in one place.
All ideas are welcome
Thanks Paul

I have added several comments to help a bit more! I want to add a screen shoot but I need 3 points!

Attached is a simple Base example with a single form. This form uses a table filter and a query to generate required calculation. Just select the conversion items in the drop down lists (generated from ‘ExchangeRates’ table) then select the button to get result.

Sample: ExchangeRate.odb

Thank you so much for your fantastic answer and your hard work, it works brilliantly. is there a way that the form can show the values and the answer is saved in the original table? a bit cheeky I know But that is probably because I didn’t explain all in my original question!!!