asked on

how to do calculation for stockin and stockout

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?

McNetic

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

aarontham

ASKER

i'm designing an inventory table. like below

ProductDesc ProductPartNumber StockIn StockOut

or

pls advice

McNetic

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?

aarontham

ASKER

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.

ASKER CERTIFIED SOLUTION

McNetic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial