Total Column

[pop]

I have a form which shows the summary of all the monthly transaction. The column are Company, Transaction Type, and total. All the amount generated in the form are form query. What I want is to add a column which would put all the amount that are in USD in CAN $. Here is the form:

And the Query:

So what I want to do is to add a column after Total which would show the amount in CAD $. The currency rate is already stored in a table. So all i need to know is how i can make this work.

Hope this is understandable, if you have any question feel free to ask

Thank you,

Pop

 

[Galaxiom]

Lookup the rate in the other table and enter an expression in the next column of the query.

Assuming the rates are in a table called RateTable, the Exchange rate is in RateField and there is a field called Currency with a value as a string called Canada.

CAD: Transaction.Amount*DlookUp("Ratefield", "RateTable", "RateTable.Currency='Canada'")

Note the use of single and double quotes.
Wouldn't need the single quotes if you had a CurrencyID number instead of Canada.

Hope this makes sense.

 

[pop]

I already tried that, but the problem that I had is that the total is the sum of the transaction and I don't know how i can do the same thing with a sum.

Thanks a lot for your help,

Pop

 

[Galaxiom]

Add the column to the query with the conversion expression as the field so it converts each transaction. Then sum that column as well.

 

[pop]

oh i see Thanks a lot for your help

 

[Galaxiom]

Also rather than looking up the conversion you would be better to add a currency field to the transaction lines and join this to the currency key in the rate table.

This would automatically choose the correct conversion rate for every transaction line. The converted column would use a simple expression and your query result would include the cost on each line in both the original and converted currencies.

A query join is also faster and simpler than a plethora of domain lookups.