CODEWITHSUNDEEP
sqlms-access
Stefano
Stefano

Reputation: 199

Access Query: the specified expression is not included

I need to update Table1.Budget with sum(Employee.[Monthly budget]) considering only employees that have the date of recruitment after Table1.Date.

I am trying to use this query:

UPDATE Table1 SET Budget = (Sum(Employee.[Monthly budget])) WHERE Table1.Date > Employee.[Date of recruitment];

but I get this error:

enter image description here

enter image description here

enter image description here

EDIT

I am using this code:

UPDATE Table1
    SET Budget = (SELECT Sum(Employee.[Monthly budget]) FROM Employee WHERE Table1.Date > Employee.[Date of recruitment]);

but I get the error message Operation must use an updatable query.

Upvotes: 1

Views: 42

Answers (3)

iDevlop
iDevlop

Reputation: 25262

If you have the 'Operation must use an updateable query' issue, just replace your subquery with a DSUM().
It's probably slower, but should do the work. 

UPDATE Table1 
SET Budget = DSUM("Monthly Budget", "Employees", "[Date of recruitement] < " & [Date])

(quickly written, but you get the idea)

By the way using Date as a field name is a VERY bad idea !!!

Upvotes: 0

Rene
Rene

Reputation: 1093

I don't see any SELECT FROM Employee:

UPDATE Table1 
SET Budget = 
SELECT Sum(Employee.[Monthly budget]) 
FROM Employee 
WHERE Table1.[Date] > Employee.[Date of recruitment];

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I think you want a subquery, perhaps:

UPDATE Table1
    SET Budget = (SELECT Sum(Employee.[Monthly budget]) FROM Employee WHERE Table1.Date > Employee.[Date of recruitment]);

Upvotes: 1

Related Questions