[RESOLVED] Calculated field / column in sql server database

[abhay_547]

Hi All,

I have two sql database tables one is mydata and another one is fxrates. Now what I want is that I have the below columns in my database.

Columns in Mydata table :
1 ) Object Currency (which contains the text like GBP, EUR, USD etc. which are currency types)
2) Value In Object Currency (Which contains the values in above mentioned currencies)
3) Period (Which has month numbers like 1, 2, 3, .i.e. Jan, Feb and Mar and so on)
4) Year (Which contains years .i.e. 2007, 2008, 2009 and 2010)

Columns in FX Rates table :
1) Exchange Rate (Which contains the exchange for all the above mentioned currencies)
2) Object Currency (which contains the text like GBP, EUR, USD etc. which are currency types)
3) Period (Which has month numbers like 1, 2, 3, .i.e. Jan, Feb and Mar and so on)
4) Year (Which contains years .i.e. 2007, 2008, 2009 and 2010)

Now I want a calculated field which will calculate value in Euros .i.e. the exchange rate is for converting value in euros.

Something like this.

Fxrates.[Exchange Rate]/mydata.[Value In Object Currency]

But while doing above calculation it should consider the criterias which are period, year and object currency as well.

I need a help on this I have macro which pull data from mydata database table to excel now is it possible for us to have this calculated column in the excel directly or we need to insert a calculated column in sql database. I am ok with both ways. Please help...

Thanks a lot for your help in advance.

[GaryMazzone]

I would calculate the the column (result) on the fly in the SQL statement you use to send the data to Excel.

[abhay_547]

Hi Gary,

Thanks a lot for your reply, can you please provide me an example as I am not very good with SQL statements in VBA. As of now I have the below sql statement.

Set cmd1 = New ADODB.Command
    cmd1.ActiveConnection = connection
    
    cmd1.CommandText = "SELECT mydata.*, 'and rest of the selection parameters
    
    Debug.Print cmd1.CommandText
    Set Results = cmd1.Execute()

First I think we will have to map exchange rates for mydata on the basis of period, year and object currency and then we can divide the same I had mentioned earlier .i.e. :

Fxrates.[Exchange Rate]/mydata.[Value In Object Currency]

Thanks a lot for your help in advance.

[GaryMazzone]

You will need to join on multile tables,,,, You should not be seting Results to an execute command but opening a record set.

[abhay_547]

Hi Gary,

Thanks a lot for your reply, actually I am through with my statement now. Following is the working sql statement

SELECT mydata.[Value In Object Currency] *
          Fxrates.[Exchange Rate] as PriceInEuro
FROM mydata
     INNER JOIN Fxrates ON
          mydata.Period = Fxrates.Period AND
          mydata.Year = Fxrates.Year AND
          mydata.[Object Currency] = Fxrates.[Object Currency]