Stock Balance by Customer with same Item Code (1 Viewer)

[NurulScare]

Hi,

From below example, I have 2 tables:
1. Stock Balance by Item Code
2. Usage by Material Code & Customer

Thus, I want to make query to get how many more need to purchase based on stock on hand minus Usage.
Below example is, I have different customer SHARE same item. so I want to get balance based on below:

Can someone Help me pleasee..

ITEMCODE	CUSTOMER	STOCK	USAGE	TO PURCHASE	Remark
A	APPLE	50	23	0	Stock Balance 27 so No need to purchase because have stock
A	LIME	27	30	3	Balance stock bring forward to next customer. Need to purchase 3 because not enough stock
A	GRAPE	0	10	10	No stock anymore, so need to purchase another 10.

 

[arnelgp]

you need to add Extra Column, MinimumReOrderLevel and ReOrderQty (optional).

MinimumReOrderLevel is the Qty you need to fill-in.
If [Stock] - [Usage] < [MinimumReOrderLevel] then
your:

[To Purchase] = MinimmReOrderLevel - ([Stock] - [Usage])

this will guarantee that you will always have stock.
that does not take into account the how many days
it takes to Order a product.

If you add the Optional ReOrderQty, then automatically
you need [To Purchase] the [ReOrderQty] when
[Stock] - [Usage] is below [MinimuReOrderLevel].

 

[NurulScare]

you need to add Extra Column, MinimumReOrderLevel and ReOrderQty (optional).

MinimumReOrderLevel is the Qty you need to fill-in.
If [Stock] - [Usage] < [MinimumReOrderLevel] then
your:

[To Purchase] = MinimmReOrderLevel - ([Stock] - [Usage])

this will guarantee that you will always have stock.
that does not take into account the how many days
it takes to Order a product.

If you add the Optional ReOrderQty, then automatically
you need [To Purchase] the [ReOrderQty] when
[Stock] - [Usage] is below [MinimuReOrderLevel].

Thanks dear.

But the problem is I cannot bring the balance to the next customer. Suppose, when I already use the stock for Customer 1st. Then Customer 2nd become less stock. heeee Can you understand my explanation?

 

[CJ_London]

databases don't work like excel - there is no previous or next record without specifying an order - and you don't have a field that could specify an order. You need a date/time field or similar.

You should not be storing calculated values like stock balance as too many things can go wrong - a usage figure is changed, the date of the usage (if you had one) changes, a stock take results in a change in the balance to name but a few.

There are a number of ways to calculate balance - use a dsum function, a subquery, a non standard join. But none will work without a basis for stating an order.

Best you can do with what you have shown is use an aggregate query to determine how much stock you need to order. Something like

SELECT tblItems.ItemCode, Stock, Used, Stock-Used AS Reorder
FROM tblItems INNER JOIN (SELECT ItemCode, sum(Usage) as Used FROM tblTransactions GROUP BY ItemCode) As qUsed ON tblItems.Itemcode=qUsed,ItemCode
WHERE Stock<Used

 

[jdraw]

See this often referenced article for Quantity On Hand by Allen Browne.