Inventory Management System

[Abdulaym]

Hi, in my inventory I have the following field



ProdName.
UnitCost.
QtySold.
TotalCost
Remaining Quantity

As you are selling product the Remaining Quantity is reducing. But when the product is finished it turn to Zero. The problem am having now is that if I enter a value e.g 20 in the field of a 'QtySold' the 'Remaining Quantity' changes to -20.

This is the code for the "Remaining Quantity"

=IIf(IsNull([Product_Id]),0,DLookUp("[Product_Quantity]","qryProduct_Quantities","[Product_Id] = " & [Product_Id]))

What I want now is that if my Remaining Quantity is 0, TotalCost=0 when QtySold*UnitCost are multiplied or a message should pop-up and say Product Finshed. I really needs help. Thanks for your usual assistance

[orange]

I recommend that you review AppInventory from Allen Browne for ideas and examples with rationale.

Always good to show your tables and relationships.

[Abdulaym]

Am new here am finding it difficult to upload my relationship n table

[Welshgasman]

Am new here am finding it difficult to upload my relationship n table

Reply, Go Advanced and use the Attachments button.

[Micron]

Or follow the instructions by clicking on the "how to attach files" button at the top of the forum page.

[Welshgasman]

Hi, in my inventory I have the following field

ProdName.
UnitCost.
QtySold.
TotalCost
Remaining Quantity

As you are selling product the Remaining Quantity is reducing. But when the product is finished it turn to Zero. The problem am having now is that if I enter a value e.g 20 in the field of a 'QtySold' the 'Remaining Quantity' changes to -20.

This is the code for the "Remaining Quantity"

=IIf(IsNull([Product_Id]),0,DLookUp("[Product_Quantity]","qryProduct_Quantities","[Product_Id] = " & [Product_Id]))

What I want now is that if my Remaining Quantity is 0, TotalCost=0 when QtySold*UnitCost are multiplied or a message should pop-up and say Product Finshed. I really needs help. Thanks for your usual assistance

Essentially you should be checking there is enough before attempting to subtract anything.

[Micron]

Not sure I agree totally, but inventory management is not my area of expertise. I might deal with the results of a negative value in the way of prompts, but as far as the calculation goes, a negative number tells you how many short you are for an order. If there is a low stock point field for inventory items, you could also know the total required for the order plus what's required to bring you back to the low stock point.

[Welshgasman]

Nor mine, but I hold the view, one cannot sell more than is in stock unless the rest is back ordered and delivered later.
Then we are entering another dimension of setting up purchase orders etc?

[Micron]

All I'm saying is that the negative value is not necessarily a bad thing to have/see, notwithstanding the ramifications of it.

[orange]

You have to be very familiar with what your rules for inventory are.
Also, you should review:
ReOrder point
ReOrder quantity

Further, it you review the Northwind2.0 videos, Kim Young describes rules for Inventory in that application. I suggest you watch

https://support.microsoft.com/en-us/...9-a2f9b941015f and
https://support.microsoft.com/en-us/...5-0714ca8dc1e2

and listen/review her comments on the rules for Inventory for these new demo databases.