Formula for Record Calculation

I have created two commands in the database expert. And I want to do the calulation of these records.

My situation is:

In table A, I have fields "Country","Amount" and "Currency".
In table B, I have fields "Currency" and "Exchange".

I want get the sum of "Amount"*"Exchange" if table A "Currency" = table B "Currency" that group by "Country"

How to write this formula?

Thank you fro helping me.Have u linked these commands by currency?|||Please tell me how to linkand what I should do after linking?
Thanks very much|||I have linked the currency field of two commands.

My situation is tableB store the currency of all countries in the world. And some of the countries using same currency. If I use a sql statement to join two table, then the records will be duplicated and cannot get the correct exchange rate of each currency.
So that I want to write a formula that caluclate the tableA amount with correct currency. But I don't know how to get the exchange rate of tableB if tableA.currency=tableB.currency.

Do you have any idea to do that?
thanks alot~~~|||I am using mysql 4.0 which is not supported subquery.

If you have any idea other than my method. Please tell me. This problem spent me a week ago~

Really thanks alot~

Formatting Numbers in an SQL Statement

Hi,
I have a table that has an ID field which is automatically incremented as each new record is added, so if I do a SELECT * FROM Table1 I get:
ID, Name
1, Billy
2, Bob
3, Tony
You get the idea. What I want to do is format the number differently when it's returned from an SQL statement so I get:
ID, Name
0001, Billy
0002, Bob
0003, Tony
So I need something like SELECT FORMATNUMBER(ID, 4), Name FROM Table1 - Does anything like this exist?
Little 'un.This type of functionality is probably better served in your code, rather than on Sql. Sql is rather slow at doing string functions.
SELECT
RIGHT( '0000' + cast( ID as varchar), 4 ) ID
FROM
table1
bill|||

Hi Bill,

Thanks for that, unfortunately it's not something I can do in my ASP code, so that should work a treat.

Little'un

sql

Format String

Hi all
I am using a control that i hand a record set to.
One of the columns in the record set displays totals.
I need to format these totals into currency.
What is the best way of doing this?
I would like it to look like this ￡2,324.40
If possible.
Thanks Heaps
IanWhy not format it in the control? That way the control can apply the
user's preferred currency format settings, which might be harder to do
in the database. In general presentational stuff is best kept separate
from data retrieval. Otherwise, you'll want to take a look at the
various style options of the CONVERT function.
David Portas
SQL Server MVP
--|||Thanks David
I don't have control over the Grid control. Some one else developed it.
Ian
"David Portas" wrote:

> Why not format it in the control? That way the control can apply the
> user's preferred currency format settings, which might be harder to do
> in the database. In general presentational stuff is best kept separate
> from data retrieval. Otherwise, you'll want to take a look at the
> various style options of the CONVERT function.
> --
> David Portas
> SQL Server MVP
> --
>