my problem is if sales.cost is < 100 then 'special' else if sales.cost => 100 'Regular'
here is my query
SELECT List.Item, List.Department, List.Group, List.Cost, [Query 2].Sales, [Query 2].Revenue , special
FROM [Sales Units], List INNER JOIN [Query 2] ON List.Item = [Query 2].Item;
Special if List.Cost
Special Less than 100
Regular 100 or greater
SELECT List.Item, List.Department, List.Group, List.Cost, [Query 2].Sales, [Query 2].Revenue , Special ,
case
when [Query 2].Sales < 100 then Special = 'Special'
when [Query 2].Sales => 100 then Special = 'Regular'
end
FROM [Sales Units], List INNER JOIN [Query 2] ON List.Item=[Query 2].Item
ORDER BY List.Item;
SELECT l.Item,
l.Department,
l.Group,
FormatCurrency(l.Cost,2,-2,-2,-2) as Cost,
q.Sales,
q.Revenue ,
CASE WHEN l.Cost < 100 THEN 'Special'
ELSE 'REGULAR' END AS 'Special'
FROM [List] l INNER JOIN [Query 2] q ON l.Item = q.Item;
For SQL
use FORMAT(l.Cost,'C','en-us')