enoch said:
hello world,
i have two tables stock and sales where one to many relationship is
created. The join type is " 2: include all records from stock table
and only those from sales where the joined fields are equal." when I
created the update query and wanted to subtract the qty sold of sales
table from the balance field in stock table, I am not able to do so.
The error I get is "did not update due to any one of the following
reasons: conversion failure, key violation, due to lock violation,
validation rule violation".
If I try one to one relationship I am able to update the balance
field
in stock table.
The datatype are same, no validation rules are given for the fields.
But in query all records are locked. Can you help me to solve this
problem?
Thankyou,
W.R.Stephen
W.R.Stephen,
I am not able to say what your exact issue is based on the information provided.
It almost sounds like you are creating a join where one side of the join is based on
aggregation (MAX, SUM, totals query, etc.).
The following is copied from the old Microsoft JET Database Engine Programmer's Guide.
Hopefully it will be of some assistance.
--------------------------------------
Updatability Restrictions for Multiple-Table Queries
To be fully updatable, a query must meet several requirements:
You must specify an explicit inner or outer join between tables. Joins created implicitly
in the WHERE clause of the SELECT statement aren't updatable. For example, the following
join isn't updatable:
SELECT
Products.ProductID,
Products.ProductName,
Categories.CategoryID,
Categories.CategoryName
FROM Categories, Products
WHERE Products.CategoryID = Categories.CategoryID;
Summary (GROUP BY), UNION, DISTINCT, and crosstab queries are never updatable. Queries
joined to one or more summary queries aren't updatable, even if you don't attempt to
modify fields from an otherwise updatable table. However, a query may be updatable if it
refers to a summary query in a sub-SELECT statement, as in the following example:
SELECT Orders.*
FROM Orders
WHERE Orders.Freight >
(SELECT Avg(Orders.Freight) AS AvgOfFreight
FROM Orders
;
In this case, fields from the Orders table are updatable.
To be able to insert new records into a table in any query, all primary key fields must be
present.
While you're updating a single record in a query, changes to certain fields may render
certain other fields nonupdatable until the edit to the record is either saved or
canceled. As soon as the user edits data on the "one" side of a query, the join key on the
"many" side can no longer be modified. Usually, the "many" side's join key is updatable.
However, because data on the "one" side was modified first, this field is temporarily
rendered unmodifiable because row fix-up would discard changes to the "one" side's data.
As soon as the change to the "one" side of the query is saved or canceled, the "many"
side's join key becomes updatable again.
A change to a multiple-table query must not create orphaned records. You can change the
join key in the "many" table to a value already present in the "one" table, but you can't
specify a nonexistent value, except in the case of outer joins.
--------------------------------------
