错误:“操作必须使用可更新的查询" -MS Access [英] Error: "Operation must use an updateable query" - MS Access
问题描述
我在MS Access中有一个表Item，看起来像这样:
I have a table Item in MS Access which looks like this:
ID ProductNumber ItemNumber Quantity UnitPrice
1 P-0001 Item-001 5 $4.00
2 P-0001 Item-002 3 $12.00
3 P-0001 Item-003 2 $6.00
4 P-0002 Item-004 1 $8.00
5 P-0002 Item-005 6 $16.00
6 P-0002 Item-006 2 $7.00
我已经使用查询Query1来计算上表的费用
I have used a query Query1 to calculate cost of above table
SELECT [Quantity]*[UnitPrice] AS Cost, *
FROM Item;
运行查询后，表如下所示.
After run query the table looks like this.
ID ProductNumber ItemNumber Quantity UnitPrice Cost
1 P-0001 Item-001 5 $4.00 20
2 P-0001 Item-002 3 $12.00 36
3 P-0001 Item-003 2 $6.00 12
4 P-0002 Item-004 1 $8.00 8
5 P-0002 Item-005 6 $16.00 96
6 P-0002 Item-006 2 $7.00 14
我已经使用子查询Query2来计算BatchCost
I have used an sub-query Query2 to calculate BatchCost
SELECT (Select Sum(T.Cost)
From Query1
AS T
Where T.ProductNumber = Query1.ProductNumber) AS BatchCost, *
FROM Query1;
运行查询后，表如下所示.
After run query the table looks like this.
ID ProductNumber ItemNumber Quantity UnitPrice Cost BatchCost
1 P-0001 Item-001 5 $4.00 20 68
2 P-0001 Item-002 3 $12.00 36 68
3 P-0001 Item-003 2 $6.00 12 68
4 P-0002 Item-004 1 $8.00 8 118
5 P-0002 Item-005 6 $16.00 96 118
6 P-0002 Item-006 2 $7.00 14 118
我还有另一个表Prices，它看起来像这样:
I have another table Prices which looks like this:
ProductNumber CostPrice
P-0001 $0.00
P-0002 $0.00
我想从Query2表中的BatchCost更新Prices表中的CostPrice.
I want to update CostPrice in Prices table from BatchCost in Query2 table.
我使用了以下查询:
UPDATE Prices INNER JOIN Query2 ON Prices.ProductNumber=Query2.ProductNumber
SET Prices.CostPrice = Query2.[BatchCost]
WHERE (((Prices.ProductNumber)=[Query2].[ProductNumber]));
但是出现错误:Operation must use an updateable query
当我在查询中使用另一个字段(如UnitPrice而不是BatchCost)时，查询有效.问题出在BatchCost字段中.
When I used another field in query like UnitPrice instead of BatchCost then query works. The problem is in BatchCost field.
推荐答案
再次，保存此聚合数据可能不是一个好主意，但是如果您必须...
Again, saving this aggregate data is probably a bad idea but if you must...
打开一个记录集，遍历记录，运行UPDATE操作.像这样:
Open a recordset, loop through records, run UPDATE action. Something like:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ProductNumber, Sum(Quantity * UnitPrice) AS SumCost FROM Item GROUP BY ProductNumber;")
While Not rs.EOF
CurrentDb.Execute "UPDATE Prices(CostPrice) VALUES(" & rs!SumCost & ") WHERE ProductNumber = '" & rs!ProductNumber & "'"
rs.MoveNext
Wend
或者没有记录集，也没有循环:
Or no recordset and no loop with this:
CurrentDb.Execute "DELETE FROM Prices"
CurrentDb.Execute "INSERT INTO Prices(ProductNumber, CostPrice) SELECT ProductNumber, Sum(Quantity * UnitPrice) AS SumCost FROM Item GROUP BY ProductNumber"
第二个选项可能是查询设计器中内置的两个查询对象，然后手动运行或从代码(宏或VBA)中调用.
This second option could be two query objects built in query designer then manually run or call from code (macro or VBA).
或在查询对象中使用DSum()并调用该查询(在VBA中使用SQL语句中的条件条件构建域聚合非常棘手):
Or use DSum() in a query object and call that query (building domain aggregate with conditional criteria in SQL statement in VBA is tricky):
UPDATE Prices SET CostPrice=DSum("Quantity*Price", "Item", "ProductNumber='" & [ProductNumber] & "'")
现在确定要在其中放入代码的事件过程，也许是单击按钮.
Now decide what event procedure you want to put code into, perhaps a button Click.
这篇关于错误:“操作必须使用可更新的查询" -MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
