Access Query Cannot have aggregate function in where clause

shieldsco
shieldsco used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
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
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
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
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.
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.
Just created subquery
Could you please show us the final solution?

TIA
Hamed NasrRetired IT Professional

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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial