Avatar of shieldsco
shieldsco
Flag for United States of America asked on

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

Microsoft AccessSQL

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
Fabrice Lambert

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.
shieldsco

ASKER
Syntax error
Error.JPG
Pavel Celba

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
shieldsco

ASKER
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
shieldsco

ASKER
I want to update the goal to 0 where the tblTransferMonthAdj Mnth = the first instance of Mnth in tblExcelProductivity
pic1.JPG
pic2.JPG
Pavel Celba

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Fabrice Lambert

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.
Pavel Celba

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.
ASKER CERTIFIED SOLUTION
shieldsco

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Pavel Celba

Could you please show us the final solution?

TIA
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Hamed Nasr

Show the answer here.
shieldsco

ASKER
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

Pavel Celba

OK, this could work. I am just not 100% why did you use DISTINCT together with GROUP BY but that's not relevant now :-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.