[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Update Query

Posted on 2006-05-25
Medium Priority
Last Modified: 2008-02-01
I have prepared following update query:
UPDATE Reception LEFT JOIN Finance ON Reception.[Invoice Login No] = Finance.[Invoice Login No] SET Finance.[Invoice No] = Reception![Invoice No], Finance.Amount = Reception![Invoice Value]
WHERE ((([Forms]![Finance1]![Invoice Login No])=[Reception]![Invoice Login No]));
This query is executed through a macro which runs after one has exited from <Invoice Login No> field of Finance form. This query gets executed only when I right click on the field and select "Remove Filter/ Sort"

If option available after right click is not used then following error message comes and the subsequent fields are not updated:
"Microsoft Office Access can't update all the records in the update query
Microsoft Office Access didn't update 0 field(s) due to a type conversion failure, 1 record(s) due to key violations, 0 record(s) due to lock violations and 0 record(s) due to validation rule violations."

Question by:pavaneesh_kumar
  • 3
  • 2
LVL 65

Expert Comment

ID: 16759459
most common cause

Your update is probably causing a field(s) which is unique, to be duplicated

check the data and what fields your updating
LVL 65

Expert Comment

ID: 16759466
why the left join, do u want to populate if null?

why dont u create a new Reception record if it does not have a invoice login no that dont exist
or simply update the amount if it does

Author Comment

ID: 16759563
The Database deals with invoice records.
First the invoices are entered in "Reception" Table where Invoice Login No is generated automatically and acts as a primary key for the table. Then it comes to finance table where another set of users enter other details. But for reference purposes three fields have same data as Reception table. In "Invoice Login No" field of "Finance" table the same invoice no is entered as was enetered in "Reception" Table. The update query picks up the "Invoice No" and "Invoice Amount" from "Reception" table.
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 44

Accepted Solution

Arthur_Wood earned 1000 total points
ID: 16759665
your update statement:

UPDATE Reception LEFT JOIN Finance ON Reception.[Invoice Login No] = Finance.[Invoice Login No] SET Finance.[Invoice No] = Reception![Invoice No], Finance.Amount = Reception![Invoice Value]

is attempting to UPDATE the field Finance.[Invoice No]  to set it to hte value in Reception![Invoice No].  This appears to somewhat suspect for two reasons.

1) when the tow fields are EQUAL, there is no need to update the field  Finance.[Invoice No], as it is already equal to the value that the statement is attempting to update it to.

2) Since this is a LEFT JOIN, you are allowing for the possibility that there is NO record in the Finance table that has an Invoice No that matches a record in the Reception tabvle, in which cate there is no record in the Finance Table where you can set the Invoice No field.

The correct SQL fro this UPDATE should read:

UPDATE Finance INNER JOIN Reception ON Finance.[Invoice Login No] = Reception.[Invoice Login No] SET Finance.Amount = Reception![Invoice Value]

LVL 65

Assisted Solution

rockiroads earned 1000 total points
ID: 16759669
if both tables have the same invoice number, u do not need to do a left join
that is selecting all records from one table regardless of whether they have a match in another table

So are you telling me, all you want to update is the Invoice Amount in the finance table from the reception table?

UPDATE Finance, Reception
SET Finance.Amount = Reception.[Invoice Value]
WHERE Finance.[Invoice Login No] = Reception.[Invoice Login No]

the above query will update all Finance records with the invoice value from its linked reception record

Is this what u want to do?

You are actually duplicating data when there is no need do

If u ever want to get at the invoice value, all you have to do is link in the reception table to get the invoice value

If the amount changes in one table but not the other, this will lead to data inconsistencies

LVL 44

Expert Comment

ID: 16759687
If there is no record in the Finance table with a matching Invoice No, then a new Finance record should be created, which does have that Invoice No.


Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 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