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 @query results using xp_sendmail

I know formatting should be handled in the client app and not in SQL Server
but this is the situation I must develop in so any help would be
appreciated.

I'm running the following in Query Analyzer

USE Pubs
DECLARE @.MessageSubject VARCHAR(50)
SELECT @.MessageSubject = 'Report'
EXEC master.dbo.xp_sendmail 'me@.mine.com',
@.query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@.subject = @.MessageSubject

The results in my email look like:

au_fname au_lname
------ ------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson

I would like the results to be like:

First Name: Abraham
Last Name: Bennet

First Name: Reginald
Last Name: Blotchet-Halls

First Name: Cheryl
Last Name: Carson

Thanks"Terri" <Terri@.spamaway.com> wrote in message
news:c0u48d$tn2$1@.reader2.nmix.net...
> I know formatting should be handled in the client app and not in SQL
Server
> but this is the situation I must develop in so any help would be
> appreciated.
> I'm running the following in Query Analyzer
> USE Pubs
> DECLARE @.MessageSubject VARCHAR(50)
> SELECT @.MessageSubject = 'Report'
> EXEC master.dbo.xp_sendmail 'me@.mine.com',
> @.query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
> @.subject = @.MessageSubject
> The results in my email look like:
> au_fname au_lname
> ------ ------------
> Abraham Bennet
> Reginald Blotchet-Halls
> Cheryl Carson
> I would like the results to be like:
> First Name: Abraham
> Last Name: Bennet
> First Name: Reginald
> Last Name: Blotchet-Halls
> First Name: Cheryl
> Last Name: Carson
> Thanks

See CHAR() in Books Online - you could try something like this:

select
'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
char(13) + char(13)
from
pubs.dbo.authors

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:403292f4$1_2@.news.bluewin.ch...
> See CHAR() in Books Online - you could try something like this:
> select
> 'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
> char(13) + char(13)
> from
> pubs.dbo.authors

This works, thanks.
@.query = 'select ''First Name: '' + au_fname + char(13) + ''LastName: '' +
au_lname + char(13) from pubs.dbo.authors',