Problem to update

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
I get error like

It should be updateable query

due to this

UPDATE [DTP Cost] SET [DTP Cost].[AE_503-101] = (SELECT Sum([DTP Expenditure].[503-101-Study-Grant]) FROM [DTP Expenditure])
WHERE [DTP Cost].[Course Code]='PDT020150004I';

Open in new window


within Access. Why?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
The only time I have seen that error is when the database file is read only.  Enable write permissions.
Can you prepare sample DB?
I don't see link between your tables. In your query all data from table [DTP Expenditure] are sumed.
Your problem is in nested query. Access doesn't allow to use in update query subqueries with posiible not unique results. In this case you can use dsum:
UPDATE [DTP Cost] SET [DTP Cost].[AE_503-101] = DSum("[503-101-Study-Grant]","[DTP Expenditure]") WHERE ((([DTP Cost].[Course Code])='PDT020150004I'));

Open in new window

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