Specified Expression not included as part of aggregate Function
Posted on 2006-11-13
I have a database with Prioperties (PropertyID) and Years.
I am trying to build a query that will give me the Sum of the RoomsCoorExp + FBExp for each Property. I've got that part working fine. I don't want to include any of the RoomsCorrExp or FB Exp if the GrossRevenue = 0 for that particular year.
I tried entering [GrossRevenues]<>0 as the criteria for my expression, and I get the Error "You tried to execute a query that does not include the specified expression 'Not [GrossRevenues]=0' as part of an aggregate function.
The SQL that does work (before I try to add in the GrossRevenue critearia) is:
SELECT tblFinancial.PropertyID, tblMASTER.HotelName, Sum([tblFinancial].[RoomsCorrExp]+[tblFinancial].[FBExp]) AS TotalCapEx
FROM tblFinancial INNER JOIN tblMASTER ON tblFinancial.PropertyID = tblMASTER.PropertyID
GROUP BY tblFinancial.PropertyID, tblMASTER.HotelName;