Access Query Error :You tried to execute a query that does not include the specified expression as part of an aggregate function. (Error 3122)

I using the following code and get an error message -
You tried to execute a query that does not include the specified expression  as part of an aggregate function. (Error 3122)

UPDATE tblLastFileDate INNER JOIN tblConsolidatedHistory ON tblLastFileDate.[Last File Date] = tblConsolidatedHistory.DateMonth SET tblLastFileDate.[File Name] = First([tblConsolidatedHistory].[FileName]);

Open in new window


Thanks
shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
First() is an aggregate function.  Queries that aggregate data are NOT updatable.  If you added a group by to the select clause to get rid of the "does not include" message, you would get the "not an updateable" query message.

You can try to do this by using a subselect that uses the First() function.
shieldscoAuthor Commented:
Pat please do not respond to my questions. Thank you
Dale FyeOwner, Developing Solutions LLCCommented:
shieldsco,

Pat Hartman is attempting to teach you, by describing the issue to you, and then describing how to resolve the issue.  This might not result in the immediate satisfaction you receive when someone hands you the answer (as I am about to do), but it serves to teach and guide you in your search for the appropriate syntax.

As she said, First() is an aggregate function and can only be used in a Group By query.  However, use of a Group By clause in a query makes that query non-updateable.  In order to resolve this problem, you may be able to use one of the following.

It appears that tblLastFileDate only contains a single record.  If it contains more than that, then you might want to add another criteria to the WHERE clause to prevent it from updating all of the records. And, as always, don't use this query on a production copy of your database until you are certain that it does precisely what you intended.

UPDATE tblLastFileDate
SET tblLastFileDate.[File Name] = (SELECT TOP 1 [FileName] FROM tblConsolidatedHistory
WHERE  tblConsolidatedHistory.DateMonth = tblLastFileDate.[Last File Date])

and if that doesn't work, try:
UPDATE tblLastFileDate
SET tblLastFileDate.[File Name] = DLOOKUP("[FileName]", "tblConsolidatedHistory", "DateMonth = #" & tblLastFileDate.[Last File Date] & "#")

HTH
Dale

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

shieldscoAuthor Commented:
Thanks
Dale FyeOwner, Developing Solutions LLCCommented:
shieldsco,

You are welcome.  When I first started answering questions on EE I was disturbed by the "experts" who simply provided the code that the users asked for.  I subscribe to the theory that if you want to feed someone, you teach them to fish, you don't give them the fish, that just builds dependency.  Because of this, I generally try to describe the solution to a question, and then provide sample code or SQL so that the user can understand why I have done what I have done in my code or SQL statement.

You will see a variety of response techniques here (EE):
1.  Some will simply provide the solution (give them a fish)
2.  Some will restate the problem, in more precise database-speak, and describe how they would do it.  When I'm pressed for time, I might do this. (teach them to fish - without the practical example)
3.  Some will restate the problem, describe how they would accomplish it, and provide a solution (teach them to fish - with rod in hand)

Keep in mind that those of us who respond, are doing it on our own time, and get nothing for it other than the satisfaction of helping others.
shieldscoAuthor Commented:
Thanks for your comments
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.