Update Query

Posted on 2006-05-25
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
    LVL 65

    Expert Comment

    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

    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

    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.
    LVL 44

    Accepted Solution

    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

    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

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now