Cannot Have Aggregate function

[vitordf]

Hi,



I am having a problem when I try to run an "Update" query, if I run the query with:

WHERE ((Tbl_EmployeeAll.LastOfDept)="Service")

The same runs OK, but when I try to run with

WHERE (((Tbl_EmployeeAll.LastOfDept)="Service") AND ((Tbl_Offers_Raw.Week)=Max([Tbl_Offers_Raw.Week])))

Then it gives me the error message:


Any idea what am I doing wrong...

Thanks in advance,

[JoeM]

If you are using an Aggregate Function in your criteria, it needs to be in the HAVING clause, not the WHERE clause.
See: http://office.microsoft.com/en-us/ac...001032238.aspx

[vitordf]

If you are using an Aggregate Function in your criteria, it needs to be in the HAVING clause, not the WHERE clause.
See: http://office.microsoft.com/en-us/ac...001032238.aspx

Thanks for the information but I don't see the aggregate so I am confused, below is the full SQL, can you perhaps check it if possible and let me now where I am wrong

INSERT INTO Tbl_OffersComplete_TEST 
( Week, Offer_Code, Description, UserID, EmployeeName, [Manager Level 4 Id], [Manager Level 3 Id], [Manager Level 2 Id], [Manager Level 1 Id], Location, Region, Event_Type, Nbr_Offers, SkillGroup )


SELECT 
Tbl_Offers_Raw.Week, 
Tbl_Offers_Raw.Offer_Code, 
Tbl_Offers_Raw.Description, 
Tbl_Offers_Raw.UserID, 
Tbl_EmployeeAll.EmployeeName, 
Tbl_EmployeeAll.[Manager Level 4 Id],
Tbl_EmployeeAll.[Manager Level 3 Id], 
Tbl_EmployeeAll.[Manager Level 2 Id], 
Tbl_EmployeeAll.[Manager Level 1 Id], 
Tbl_EmployeeAll.Location, 
Tbl_Centres.Region, 
Tbl_Offers_Raw.Event_Type, 
Tbl_Offers_Raw.Nbr_Offers, 
Tbl_SKGDetail.SkillGroup


FROM
((Tbl_Offers_Raw INNER JOIN Tbl_EmployeeAll ON Tbl_Offers_Raw.UserID = Tbl_EmployeeAll.UserID) INNER JOIN 
Tbl_SKGDetail ON Tbl_Offers_Raw.UserID = Tbl_SKGDetail.UserID) INNER JOIN 
Tbl_Centres ON Tbl_EmployeeAll.Location = Tbl_Centres.OrgLocation


WHERE ((Tbl_EmployeeAll.LastOfDept)="Service")
HAVING ((Tbl_Offers_Raw.Week)=Max([Tbl_Offers_Raw.Week]));

My aim is to only insert the records from the latest week within the Tbl_Offers_RAW.

Thanks again,

[kazaccess]

You could also include a Select statement in the second where condition:
WHERE (((Tbl_EmployeeAll.LastOfDept)="Service") AND [Tbl_Officers_Raw.Week] = (Select Max([Tbl_Offers_Raw.Week]) from TableName)

[JoeM]

My aim is to only insert the records from the latest week within the Tbl_Offers_RAW.

I don't think you can use MAX in your criteria if it is not anywhere in your Select statement.

So you will either need to use a nested query (like kazaccess showed), or do it in a series of two queries (the first one to find the MAX value).