• 0

Stock management program


Question

Dear all

One of the modules of my program contains an inventory, which is like a place for storing raw materials, each with a specific price, however, you may have two or more entries for the same material, with different price. I want a way so that when for example i want to withdraw 50 units of a specific material, and i have 60 units in stock, but 40 of which were prices $20 and the remaining 20 were priced say $10, i want the program to tell me that the transaction went as follows: 40 from the ones priced at $20 and 10 from the ones priced at $10. Is there a way to do this? i am using access but SQL solutions are also welcome.

Thanks a lot in advance

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Welcome!

What you could do would be to introduce some 'grouping' ID into the inventory table, so that each product entry that is the same has the same grouping ID. With this you could then select as many items as possible from the item with the lowest price (use grouping ID to search), once you have selected as many as you can (40) you then look for the next cheapest and select as many as possible of them (20).

Just put this in some sort of loop, with some checking for if you run out of product.

Link to comment
Share on other sites

  • 0

You could add a sub-stockID to your data table that's got the product's stock ID stuck to the price.

StockID - InventoryID
111345	111345_1150

That way you can still pull up the product by its key, but there's a subkey that keeps it from 'mingling' with differently priced items of the same product.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.