subtraction in access from two tables

[gideon daniel]

i want to subtract value from two different tables one is the inventory table and the second is the sales table. i want the quantity sold from the sale table to be subtracted from the quantity in the inventory table base on the name of the product. which is in both tables

[NeoPa]

I'll start by telling you that, by asking this question, you are indicating a fundamental lack of understanding of your basic issue that will almost certainly lead you into difficulties. Without something to indicate that a sale has already been applied, you have nothing to stop such an update from being repeated inappropriately. In many cases too, it is unnecessary to update data in place when the result can be calculated from the existing data (See Database Normalisation and Table Structures).

Next, as it's also very important, you never want to use string values to identify items. It gets messy and easily and frequently leads to problems where things are similar but not recognised as the same due to hard to notice differences. In short, it's never a good idea.

All that said, the simple SQL structure of such a command is :

UPDATE [tblInventory] AS [tI]
       INNER JOIN
       [tblSales] AS [tS]
   ON  tI.ProductName = tS.ProductName
SET    tI.Quantity = tI.Quantity - tS.QuantitySold

[PreethiGowri]

UPDATE inventory_table 
LEFT JOIN sale_table ON inventory_table.name = sale_table.name
SET inventory_table.quantity = (inventory_table.quantity - sale_table.quantity) 
WHERE inventory.name = pen;

[NeoPa]

While that's very similar to what is already posted, it differs in two aspects - both of which changes are problems.

1. The LEFT JOIN is not correct as you would certainly not want to process Inventory records where there were no matching Sales records.
2. The WHERE clause, if it were required - and I don't believe it is, would need to have the value surrounded in quotes (Quotes (') and Double-Quotes (") - Where and When to use them) to show that it is a string literal and not a variable of any sort.

Any other differences are inconsequential and have no bearing on the fundamental effect of the SQL code (so it's mostly right, even if it has a couple of small errors).