Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-09-17
4
Medium Priority
?
152 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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

Author Closing Comment

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

Thanks,
gdunn59
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
I came across an unsolved Outlook issue and here is my solution.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

580 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