Error in Update Query

[Neal]

I have created the follwing update query:

UPDATE tblEntitlements INNER JOIN TotalHolidaysBooked1 ON tblEntitlements.EmployeeID = TotalHolidaysBooked1.EmployeeID SET tblEntitlements.CurrentEntitlement = tblEntitlements!TotalEntitlement-[SumOfDays];

TotalHolidaysBooked1 is another query which sums the number of days holiday booked for each employee from this query the expression [SumOfDays] is created by this query - see below

SELECT DISTINCTROW HolidayQuery.EmployeeID, HolidayQuery.EmployeeName, HolidayQuery.Branches, Sum(HolidayQuery.Days) AS SumOfDays
FROM Holidays, HolidayQuery
GROUP BY HolidayQuery.EmployeeID, HolidayQuery.EmployeeName, HolidayQuery.Branches
HAVING (((HolidayQuery.EmployeeName) Is Not Null));

I get the following error message when I run the update query

"Operation Must Use An Updateable Query"

Can anybody propose a solution to this issue - many thanks

 

[John Spencer (MVP)]

Can't use any aggregate SQL functions (sum, avg) etc in an update query or any
nested queries used by the update query. You might try using one of the vba
aggregate functions (DSUM) for example. Without a better understanding of your
tables, I am pretty sure that the following will NOT work, but it should give
you the idea.

UPDATE tblEntitlements
SET tblEntitlements.CurrentEntitlement = tblEntitlements!TotalEntitlement-
DSUM("Days","HolidayQuery","EmployeeID = " & EmployeeID);

You might TRY the following based on your second query. I'm not sure if it will
work, but it is worth a shot.
DLookup("SumOfdays","TotalHolidaysBooked1","EmployeeID = " & EmployeeID)

This will probably be slow.