Link to home
Start Free TrialLog in
Avatar of shieldsco
shieldscoFlag 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

Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

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

ASKER

Syntax error
Error.JPG
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

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
I want to update the goal to 0 where the tblTransferMonthAdj Mnth = the first instance of Mnth in tblExcelProductivity
pic1.JPG
pic2.JPG
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

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.
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
Avatar of shieldsco
shieldsco
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Could you please show us the final solution?

TIA
Show the answer here.
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

OK, this could work. I am just not 100% why did you use DISTINCT together with GROUP BY but that's not relevant now :-)