Access Query Cannot have aggregate function in where clause

I'm getting the following error:
Cannot have aggregate function in where clause

UPDATE tblExcelProductivity INNER JOIN tblTransferMonthAdj ON (tblExcelProductivity.FY = tblTransferMonthAdj.FY) AND (tblExcelProductivity.FullName = tblTransferMonthAdj.FullName) AND (tblExcelProductivity.Mnth = tblTransferMonthAdj.Mnth) SET tblExcelProductivity.Goal = 0
WHERE (((tblTransferMonthAdj.Mnth)=First([tblExcelProductivity]![Mnth])));

Open in new window

shieldscoAsked:
Who is Participating?
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.

Fabrice LambertConsultingCommented:
Try with a sub query:
UPDATE tblExcelProductivity INNER JOIN tblTransferMonthAdj
    ON tblExcelProductivity.FY = tblTransferMonthAdj.FY
    AND tblExcelProductivity.FullName = tblTransferMonthAdj.FullName
    AND tblExcelProductivity.Mnth = tblTransferMonthAdj.Mnth
SET tblExcelProductivity.Goal = 0
WHERE tblTransferMonthAdj.Mnth = (SELECT First[tblExcelProductivity].[Mnth]
    FROM tblExcelProductivity);

Open in new window

Side note:
Indent your code, so it is easyer to read.
shieldscoAuthor Commented:
Syntax error
Error.JPG
pcelbaCommented:
Aggregate function needs to know what should be the group for aggregation. And to use them in WHERE is not allowed this way.

Do I understand it needs to update just the first month for each FY and Full name? But your WHERE clause uses the First value from the whole table.

Maybe if you explain in your own words what the UPDATE query should do then we may help to build the WHERE clause.

You could try the following modification:
UPDATE tblExcelProductivity  SET tblExcelProductivity.Goal = 0
 INNER JOIN tblTransferMonthAdj 
    ON (tblExcelProductivity.FY = tblTransferMonthAdj.FY) AND (tblExcelProductivity.FullName = tblTransferMonthAdj.FullName) AND (tblExcelProductivity.Mnth = tblTransferMonthAdj.Mnth)
 WHERE (tblTransferMonthAdj.Mnth)= (SELECT MIN([Mnth]) FROM [tblExcelProductivity])

Open in new window

Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

shieldscoAuthor Commented:
I want to update the goal to 0 where the tblTransferMonthAdj Mnth = the first instance of Mnth in tblExcelProductivity

tblTransferMonthAdj
FY      FullName      Mnth      CountOfMnth
2019      Gregory, Paula      Oct      2
2019      Plott, James      Dec      2
2019      Plott, James      Jan      2
2019      Shurtliff, Jaya      Nov      2
2019      Shurtliff, Jaya      Oct      2

tblExcelProductivity
Text58      CalDiff      FY      DailyGoal      Days      Mnth      FullName      FieldOffice      TrnFieldOffice      TrnDate      Actual      Goal      Diff      EOD      Term Date      MultipleFO
-61      0      2019      4      22      Oct      Gregory, Paula      PHOENIX      CLEVELAND            0   88      -61      9/4/2016            Yes
-75      0      2019      4      22      Oct      Gregory, Paula      PHOENIX      CLEVELAND            13      88      -75      9/4/2016            Yes
shieldscoAuthor Commented:
I want to update the goal to 0 where the tblTransferMonthAdj Mnth = the first instance of Mnth in tblExcelProductivity
pic1.JPG
pic2.JPG
pcelbaCommented:
OK, neither MIN() nor First() can be used in this case... and if your table does not contain some unique ID you cannot be sure the update will do what you need.

What seems to be easier is to fix your data which means to sum the two (or more) months for each person having more rows for one month. You may sum the Actual column and calculate max for the rest. Something like:
SELECT MAX([Days]) as [Days], MAX([Months] as [Months], …, SUM([Actual]) as [Actual], MAX([Goal]) as [Goal] 
  FROM [tblExcelProductivity]

Open in new window

Fabrice LambertConsultingCommented:
I want to update the goal to 0 where the tblTransferMonthAdj Mnth = the first instance of Mnth in tblExcelProductivity
Hmm, unless data are sorted with an ORDER BY clause, there are no guarantees about how datas are stored in a database.

So retrieving or performing operations on the "First instance" without criterias make little to no sens.
pcelbaCommented:
I would guess it makes sense here because the Goal should be just in one row. Everything else is either duplicated from other rows (name, date, office, ...) or cumulative (Actual hours). Thus the sum of the multiple rows in each group should be the right solution.
shieldscoAuthor Commented:
Just created subquery

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
pcelbaCommented:
Could you please show us the final solution?

TIA
Hamed NasrRetired IT ProfessionalCommented:
Show the answer here.
shieldscoAuthor Commented:
SELECT DISTINCT qrytblTransferMonthAdj.FY, qrytblTransferMonthAdj.FullName, qrytblTransferMonthAdj.Mnth, qrytblTransferMonthAdj.CountOfMnth, Last(tblExcelProductivity.FieldOffice) AS FO INTO tblExcelProdTransferGoalAdj
FROM tblExcelProductivity INNER JOIN qrytblTransferMonthAdj ON tblExcelProductivity.FullName = qrytblTransferMonthAdj.FullName
GROUP BY qrytblTransferMonthAdj.FY, qrytblTransferMonthAdj.FullName, qrytblTransferMonthAdj.Mnth, qrytblTransferMonthAdj.CountOfMnth;

Open in new window



UPDATE tblExcelProductivity INNER JOIN tblExcelProdTransferGoalAdj ON (tblExcelProductivity.FY = tblExcelProdTransferGoalAdj.FY) AND (tblExcelProductivity.FieldOffice = tblExcelProdTransferGoalAdj.FO) AND (tblExcelProductivity.Mnth = tblExcelProdTransferGoalAdj.Mnth) AND (tblExcelProductivity.FullName = tblExcelProdTransferGoalAdj.FullName) SET tblExcelProductivity.Goal = 0;

Open in new window

pcelbaCommented:
OK, this could work. I am just not 100% why did you use DISTINCT together with GROUP BY but that's not relevant now :-)
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.