How to select and update from the same table in Ms Access 2007

advertisements

I want to be able to select and update my current stock table

This is my query:

UPDATE current_stock
SET current_stock.quantity =
    (SELECT quantity
      FROM (SELECT * FROM current_stock) As current_stock1
    WHERE current_stock1.stock_id=2)
WHERE stock_id=1;

If i run the nested select without the update it works fine, but using it with the update statement always returns error: operation must use updatable query

Actual Problem:

I want to deduct current stock quantity by the required quantity field in the table Bom_dell according to the corresponding laptop id

Syntax error: (missing operator) in query express Dlookup ..

I'm guessing the nested Dlookup is not working

UPDATE current_stock
SET current_stock.quantity =
    DLookup("quantity", "current_stock", "stock_id=1")-DLookup("req_quantity","Bom_dell","lap_id=DLookup("lap_id","laptop_info","model_name="Dell Inspiron"")")
WHERE stock_id=1;

According to you're suggestions I changed the code to contain SELECT statement and it works:

UPDATE current_stock
SET current_stock.quantity =
    DLookup("quantity", "current_stock", "stock_id=1")-DLookup("req_quantity","Bom_dell","lap_id=(SELECT lap_id FROM laptop_info WHERE model_name='Dell Inspiron')")
WHERE stock_id=1;

All I need to do now, is add a for loop to deduct all the quantities in the current stock table

http://tinyurl.com/7eghddq << Here is the link to the image of my relationship table


Domain Aggregate Functions can be used to avoid the "operation must use updateable query" error.

In your case, it seems you want to replace the quantity for stock_id=1 with the quantity from stock_id=2. If I understood that correctly, try this UPDATE query.

UPDATE current_stock
SET current_stock.quantity =
    DLookup("quantity", "current_stock", "stock_id=2")
WHERE stock_id=1;