?
Solved

Specified Expression not included as part of aggregate Function

Posted on 2006-11-13
6
Medium Priority
?
296 Views
Last Modified: 2008-02-01
Hello,

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;

Any thoughts?

Thanks!
cdmac
0
Comment
Question by:cdmac2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17933447
An alternative way, try this
though it might still complain due to +

SELECT tblFinancial.PropertyID, tblMASTER.HotelName, Sum([tblFinancial].[RoomsCorrExp])+Sum([tblFinancial].[FBExp]) AS TotalCapEx
FROM tblFinancial INNER JOIN tblMASTER ON tblFinancial.PropertyID = tblMASTER.PropertyID
GROUP BY tblFinancial.PropertyID, tblMASTER.HotelName
0
 
LVL 1

Author Comment

by:cdmac2
ID: 17933547
Rockiroads,

Thanks for your response.  The Statment I wrote above DOES work (I tried your statement too, and it also works).  What I can't figure out how to do is get the "GrossRevenues<>0" Criteria in there.

Thx!
0
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 17933607
SELECT tblFinancial.PropertyID, tblMASTER.HotelName, Sum([tblFinancial].[RoomsCorrExp])+Sum([tblFinancial].[FBExp]) AS TotalCapEx
FROM tblFinancial INNER JOIN tblMASTER ON tblFinancial.PropertyID = tblMASTER.PropertyID
WHERE <WhichTable?>.GrossRevenues <> 0
GROUP BY tblFinancial.PropertyID, tblMASTER.HotelName;

Add the third line and be sure to replace <WhichTable?> with the correct table name - either tblFinancial or tblMaster
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 17933654
ok, gotcha
I see GRayL has given u the answer, obviously too slow in responding today
I think it has something to do with tree cutters!

0
 
LVL 1

Author Comment

by:cdmac2
ID: 17933679
Worked like a charm!

I'm feeling a little off myself.  The solution was very simple.. for some reason I was stuck in the HAVING statemnt.

Thanks guys!


0
 
LVL 44

Expert Comment

by:GRayL
ID: 17933746
Thanks, glad I could help.  

rocki, the vision is slowly improving.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question