how to do calculation for stockin and stockout

aarontham
aarontham used Ask the Experts™
on
how to design a table which can do below requiment.
how to do calculation for stockin and stockout.
how to add stockin if no totalstockin field?
 like now i have invoice and how to - from stock if the stock no total field?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Could you please explain in detail which data you have and what you want to calculate. Preferable with an existing table and a sample.

Author

Commented:
i'm designing an inventory table. like below

ProductDesc   ProductPartNumber   StockIn  StockOut

or

pls advice

Commented:
I still don't get it completely. So you have said table. StockIn is a how much items ever went into stock, stockout is how much items ever left stock? Then an invoice would be

UPDATE inventory SET StockIn=StockIn+<x> WHERE ProductPartNumber = <somenumber>

To calculate current stock

SELECT StockIn - StockOut FROM inventory WHERE ProductPartNumber = <somenumber>

<x> is the number of items in the invoice, <somenumber> your part number.

Did I get this right?

Author

Commented:
HI

i  need database table design.
let say i have invoice 5 pcs same items need to - from inventory.

so should have a invoice table like below

InvoiceTable
InvoiceNumber        ProductPartNumber        Q'ty       UnitPrice.
123                             AC001                           5           1,000
124                             HP001                            6           1,200
1)should I have a totalAmount in InvoiceTable ?

and I also should have a Goods received table like below

GoodsIreceivedTable
GRNNumber        ProductPartNumber        Q'ty       UnitPrice.
121                          HP001                        12            1,4000
122                          AC001                        12            1,1000
123                          AC001                           5           1,000
124                          HP001                            6           1,200



and in inventorytable should look like below

inventoryTable
ProductPartNumber         ProductSpec                                        ProductGroup   ProductBrand
AC001                             1.6Ghz intel CPU with 4GB RAM         Laptop               Acer        
HP001                              2.6Ghz AMD CPU with 2GB Ram        Desktop             HP

2)in inventorytable should i have a totalqty or i manual do calculation. when i need to check a total stock calculation.

Commented:
Ok, I see. I would add a totalqty to the inventory table. You'll have to make sure it's updated correctly after each update to the received and invoice table; however, it also gives you the possibility to do manual corrections to your inventory table when you do an actual inventory of your stock.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial