asked on 10/4/2011

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

I have an append query that generates the following error message:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Each group by expression must contain at least one column that is not an outer reference. (#164)

I am sure that this query worked at one time, but now I am getting the error message.

Does anyone have an idea how I can get around this problem?

INSERT INTO tblTempPSPHeader ( PSPReferralId, CheckAmount, CheckDate )
SELECT tblTempPSPDetail.PSPReferralId, Sum(tblTempPSPDetail.AmountToPay) AS SumOfAmountToPay, Date() AS Expr1
FROM tblTempPSPDetail
GROUP BY tblTempPSPDetail.PSPReferralId, Date();

Open in new window

Microsoft Access

Last Comment

Douglas Cummings

10/5/2011

ASKER CERTIFIED SOLUTION

mbizup

10/4/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Jeffrey Coachman

10/4/2011

Does this run on it's own and return the correct daytaset:

SELECT tblTempPSPDetail.PSPReferralId, Sum(tblTempPSPDetail.AmountToPay) AS SumOfAmountToPay, Date() AS Expr1
FROM tblTempPSPDetail
GROUP BY tblTempPSPDetail.PSPReferralId, Date();

Douglas Cummings

10/4/2011

ASKER

Thanks for the help! Worked kike a charm. It was today's date that I wanted to insert into the CheckDate field.

mbizup

10/5/2011

Glad to help out!

You mentioned that this had worked for you in the past. Â Just curious - Have you made a switch from Access to SQL for your back end?

To my knowledge, your syntax would have worked for you previously if you had been using an Access back end.

Douglas Cummings

10/5/2011

ASKER

Originally the temporary tables were local. But, due to other factors, I had to use a SQL database instead. That is when the problem developed.

Thanks again.

Douglas Cummings

10/5/2011

ASKER

I actually resolved the issue on my own, but not quite as elegantly as you. I figured it was the Date() expression and I simply deleted it from the query and added the date to the records via an update query. I like your solution better as it saved me a step.