Another Convert Jet to TSQL

[RobFreeman]

The following works fine in Access 2003. However, in SQL it fails with "Invalid Column Name".

In Access you can create a new column, in our case "Margin" and immediately use it in futher calculations. I am a total rookie in TSQL so would like some ideas on the best way to solve this type of problem.


SELECT
Products.ProductName,
Products.UnitPrice,
Products.Cost,
[UnitPrice]-[Cost] AS Margin,
[Margin]*100/[UnitPrice] AS MarginPct
FROM Products;

 

[gmmastros]

You just need to copy the 'margin' column, like this...

SELECT
Products.ProductName,
Products.UnitPrice,
Products.Cost,
[UnitPrice]-[Cost] AS Margin,
([UnitPrice]-[Cost])*100/[UnitPrice] AS MarginPct
FROM Products

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom