stock store

[a]

I have 2 table

Stock_Table

Take _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



Take _Table contain this field

(Take mean Pull or get from Stock table)

TakedId Primary key

StockId Foreign Key

TakeName

Quantity

DateOftake



TakeId StockId TakeName Quantity
DateOfTake

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 (one
Stock_Table many Take _Table)

What I want to tell you:

How can I subtract Take_Table from Stock_table

I can't Imagine that

Because

Stock_table will rise(rise mean increased) continuously

Take_table will rise also by item pulled from stock

What should I do?

Query Or Form Or Report

this is a sample (is this good sample for inventory) or not

 

[Dale Fye]

I assume that what you are really trying to ask is:

How can I determine what I have in stock on a given date?

I would assume your query would look something like:

SELECT StockID, StockName, SUM(Quantity) as OnHand
FROM
(SELECT StockID, StockName, Quantity
FROM Stock_Table
WHERE Date_of_Add < #2/13/2008#
UNION ALL StockID, TakeName, -1 * Quantity
FROM Take_Table
WHERE DateOfTake < #2/13/2008#) as Receipt_and_Sales
GROUP BY StockID, StockName

HTH
Dale