access 2007: sql update query return some error

Posted on 2012-08-12
Medium Priority
Last Modified: 2012-08-27
I have a query below in my access

UPDATE invoice SET invoice.[nett profit] = (select [amount]-[supplier cost] from invoice as profit);

when I execute this query it return me with error " operation must use an updateable query"

anyone know this ? and how to fix this error
Question by:motioneye
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38286099
try this

UPDATE invoice SET invoice.[nett profit] =   [amount]-[supplier cost]


UPDATE invoice SET invoice.[nett profit] =   invoice.[amount]-invoice.[supplier cost]
LVL 52

Accepted Solution

Gustav Brock earned 1000 total points
ID: 38286837
First, (select [amount]-[supplier cost] from invoice as profit) can return more than one record.
Second, you are updating a table with data from itself but other records.

Write the output (select [amount]-[supplier cost] from invoice as profit) to a temp table and update from that - or rewrite (select [amount]-[supplier cost] from invoice as profit) to return one value only.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question