Solved

Each group by expression must contain at least one column that is not an outer reference

Posted on 2014-09-17
4
136 Views
Last Modified: 2014-10-07
I have a query (see code below), but when I run the query, I get the following error:

"Each group by expression must contain at least one column that is not an outer reference"

What is causing this?  This wasn't an issue until I upgraded to MS Office 2010/Access 2010 and Windows 7.

SELECT tblEmployee_Audits.InquiryNum, tblEmployee_Audits.Employee, tblEmployee_Audits.Manager_Name, tblEmployee_Audits.Quality_Review_Date, tblEmployee_Audits.Auditor_Name, tblEmployee_Audits.Scorecard, tblEmployee_Audits.Department, tblEmployee_Audits.Region, tblEmployee_Audits.System, tblEmployee_Audits.Audit_Date, tblEmployee_Audits.Audit_Status, First(tblEmployee_Audits.Audit_Notes) AS FirstOfAudit_Notes, tblEmployee_Audits.Action_Required, tblEmployee_Audits.Action_REQ_Reason, tblEmployee_Audits.Date_Entered, tblEmployee_Audits.Completed_Date, First("") AS txtAuditStatus, tblEmployee_Audits.Sub_AuditType INTO tblAudits_ForEmail
FROM tblEmployee_Audits
GROUP BY tblEmployee_Audits.InquiryNum, tblEmployee_Audits.Employee, tblEmployee_Audits.Manager_Name, tblEmployee_Audits.Quality_Review_Date, tblEmployee_Audits.Auditor_Name, tblEmployee_Audits.Scorecard, tblEmployee_Audits.Department, tblEmployee_Audits.Region, tblEmployee_Audits.System, tblEmployee_Audits.Audit_Date, tblEmployee_Audits.Audit_Status, tblEmployee_Audits.Action_Required, tblEmployee_Audits.Action_REQ_Reason, tblEmployee_Audits.Date_Entered, tblEmployee_Audits.Completed_Date, tblEmployee_Audits.Sub_AuditType
HAVING (((tblEmployee_Audits.InquiryNum)=[Forms]![frmEmployee_Audits]![InquiryNum]));

Open in new window

0
Comment
Question by:gdunn59
  • 3
4 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 40328558
gdunn59:

Can you post a sample database?  DO NOT INCLUDE REAL DATA!

I don't believe the parenthesis are necessary.  Try the following:
HAVING tblEmployee_Audits.InquiryNum = [Forms]![frmEmployee_Audits]![InquiryNum];

Regards,

AielloJ
0
 

Author Comment

by:gdunn59
ID: 40337585
Aiello,

It's just to difficult to provide the database because there are too many tables that are being used in all the queries/reports that contain confidential data.

I did try your suggestion about the parenthesis and the HAVING statement that you provided, but it didn't make a difference.  Do you or anyone else have any further suggestions that I can try.

As I mentioned before, this wasn't an issue until I upgraded to Windows 7 and Office 2010.

Thanks,

gdunn59
0
 

Accepted Solution

by:
gdunn59 earned 0 total points
ID: 40357826
I ended up doing a Dlookup for the Memo field, and that worked.

Thanks everyone for your assistance.

gdunn59
0
 

Author Closing Comment

by:gdunn59
ID: 40365500
I accepted my own comment because that is what resolved the issue.

Thanks,
gdunn59
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now