updating a table from query (1 Viewer)

[Ramnik]

Hello everyone ,
i want to update a field of the table from a field of query .

this is the syntax :
UPDATE tbl_PresentStockAll INNER JOIN qry_BroachStock ON tbl_PresentStockAll.ProductId = qry_BroachStock.ProductId SET tbl_PresentStockAll.Broach = [qry_BroachStock].[BalanceQuantity];

and attached is the image

but i am getting error of
"operation must use an updateable query"

any help ???
thankssssss

 

[plog]

You keep running into these issues because you are not using your database properly (i.e. http://www.access-programmers.co.uk/forums/showthread.php?t=241388). This value shouldn't be stored in 2 places. It probably shouldn't be stored at all and instead calculated when you need it.

Your structure is off and you are storing values you shouldn't be. These issues (and the ones that will come in the future) are why we keep beating on you to fix the foundation of your database.

 

[Ramnik]

i understand ur point but lot of time needed to replace every stuff i have developed ... so i need this urgently . in the whole redesign i will normalize the database first . thanks ....
and it is a question and i expect the answer . i respect ur solution but the situation looks different when seeing from different perspective . so at my place i need the temporary solution this time. thankssss

 

[JHB]

"qry_BroachStock" is a query or?
If yes, the show "qry_BroachStock".

 

[Ramnik]

"qry_BroachStock" is a query or?

Yes qry_broachstock is a query and i need to update a field(Broach) in table(tbl_PresentStockAll) from this query's field(BalanceQuantity) .

If yes, the show "qry_BroachStock".

sorry i didnt understand this.

 

[Ramnik]

here is the sql :

UPDATE tbl_PresentStockAll INNER JOIN qry_BroachStock ON tbl_PresentStockAll.ProductId = qry_BroachStock.ProductId SET tbl_PresentStockAll.Broach = [qry_BroachStock].[BalanceQuantity];

 

[jdraw]

Please show the sql for qry_BroachStock.

It seems you are far into operations and have not done your planning and design. It's a little like building a city with no planning; or building a jumbo jet without planning, design and testing.

 

[Ramnik]

SELECT qry_StockMs.[Product Id] AS ProductId, qry_StockMs.[Product Name] AS ProductName, qry_StockMs.[Input Quantity] AS InputQuantity, Nz(([SumOfPassQuantity]),0) AS PassQuantity, Nz(([SumOfRejectQuantity]),0) AS RejectQuantity, qry_TotalBroachProduction.SumOfTotalQuantity AS TotalQuantity, [Input Quantity]-(Nz([TotalQuantity],0)) AS BalanceQuantity, tbl_MasterMs.ProcessId
FROM tbl_MasterMs INNER JOIN (qry_StockMs LEFT JOIN qry_TotalBroachProduction ON qry_StockMs.[Product Id] = qry_TotalBroachProduction.ProductCode) ON tbl_MasterMs.ProductId = qry_StockMs.[Product Id]
ORDER BY qry_StockMs.[Product Id];

 

[JHB]

Do the table update if you put in a number by "hard code"?

UPDATE tbl_PresentStockAll INNER JOIN qry_BroachStock ON tbl_PresentStockAll.ProductId = qry_BroachStock.ProductId SET tbl_PresentStockAll.Broach = 10;

I can't see why the table shouldn't update running you update query.