Update Inventory

[Guest]

I have an inventory table (fields item, StockNo, QuantityOnHand) and a
"Release" table (fields: item, releaseDate, quantity).
User fills out Release information (to release new computers). Quantity is
always one. I need my form to deduct the quantity in release table from
quantity on hand in Inventory table. I know I can do an update query, but not
sure what criteria to put in it so that only that one record is updated.

Thanks.

 

[James]

Hi

Create a query and paste the following into the SQL view:
UPDATE inventory INNER JOIN release ON inventory.item = release.item
SET inventory.quantityonhand = [quantityonhand]-1;

Make sure you test it first on test data!

Regards
James