This is probably an easy question but how can I subtract T1.Sales from SalesComp a calculated field that is using "AS." It's not a field in the table so I'm having problems. I know I could write the formula again to get SalesComp but would like to just use the field name if possible.
SELECT T1.StoreNumber, T1.Date, T1.Sales, DateAdd(DAY,-371,T1.Date) AS DateComp,
(Select Sum (Sales)
WHERE T1.StoreNumber = Sales.StoreNumber and Sales.Date = DateAdd(DAY,-371,T1.Date))
AS SalesComp, T1.Labor, T1.CashOS, T1.Comment
FROM Sales AS T1
ORDER BY T1.Date DESC, T1.StoreNumber ASC