We help IT Professionals succeed at work.

Access Query Cannot have aggregate function in where clause

154 Views
Last Modified: 2019-02-04
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

Comment
Watch Question

Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Syntax error
Error.JPG
CERTIFIED EXPERT

Commented:
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

Author

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

Author

Commented:
I want to update the goal to 0 where the tblTransferMonthAdj Mnth = the first instance of Mnth in tblExcelProductivity
pic1.JPG
pic2.JPG
CERTIFIED EXPERT

Commented:
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 LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT

Commented:
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.
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
Could you please show us the final solution?

TIA
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Show the answer here.

Author

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

CERTIFIED EXPERT

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