Sales & warehouse store or Stock Store

[KARL DEWEY]

Use these three queries ---
Stock_Total ---
SELECT Stock_Table.StockName, Sum(Stock_Table.Quantity) AS SumOfQuantity
FROM Stock_Table
GROUP BY Stock_Table.StockName;

Sales_Total ---
SELECT Sales_Table_1.SalesName, Sum(Sales_Table_1.Quantity) AS SumOfQuantity
FROM Sales_Table_1
GROUP BY Sales_Table_1.SalesName;

SELECT Stock_Total.StockName,
[Stock_Total].[SumOfQuantity]-[Sales_Total].[SumOfQuantity] AS Balance
FROM Stock_Total LEFT JOIN Sales_Total ON Stock_Total.StockName =
Sales_Total.SalesName;

 

[a]

I have 2 table

Stock_Table

Sales_Table

Stock_Table Contain This Field

StockId Primary Key

StockName

Quantity

Date_of_Add



StockId StockName Quantity Date_of_add

1 a1 computer 10 1/1/2008

2 b2 printer 20 1/1/2008

3 a1 computer 10 15/1/2008

4 b2 printer 60 20/1/2008



Sales_Table contain this field

(Sales mean Pull or get from Stock table)

SalesId Primary key

StockId Foreign Key

SalesName

Quantity

DateOfSales



TakeId StockId SalesName Quantity
DateOfSales

1 1 a1 computer 5
9/1/2008

2 1 a1 computer 1
10/1/2008

3 1 a1 computer 1
11/1/2008

4 2 b2 printer 6
2/1/2008

5 2 b2 printer 6
3/1/2008



There are a relation between 2 table one to many relation ship

What I want to tell you:

How can I subtract Sales_Table from Stock_table

I can't Imagine that

Because

Stock_table will rise(rise mean increased) continuously

Sales_table will rise also by item pulled from stock

What should I do?

Query Or Form Or Report