Solved

Specified Expression not included as part of aggregate Function

Posted on 2006-11-13
6
292 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
  • 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 500 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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