how do I set a criteria for a percentage calculation in sql

[martinr]

I have a query that calculates a percentage that works fine.
the sql is like so:


SELECT Table1.cost, Table1.sales, Table1.location, [sales]-[cost] AS GrossProfit, [GrossProfit]/[sales] AS GrossProfitPC
FROM Table1;


If I try to set criteria to select records based on the calculated value of the expression (eg; only select records with a GrossProfit percentage > 50) when I run the query it asks for a parameter as if the field [GrossProfit ] were a parameter.
Is there a way i can set a criteria based on the value of the percentage field?

 

[JHB]

Try the below, (it is not tested):

SELECT Table1.cost, Table1.sales, Table1.location, [sales]-[cost] AS GrossProfit, [GrossProfit]/[sales] AS GrossProfitPC
FROM Table1 WHERE ([sales]-[cost])/[sales] >0.5;

 

[MarkK]

Or you could sub-query, like . . .

SELECT * FROM (
   SELECT Table1.cost, Table1.sales, Table1.location, 
      [sales]-[cost] AS GrossProfit, 
      [GrossProfit]/[sales] AS GrossProfitPC
   FROM Table1
   )
WHERE GrossProfitPC > 0.5