Stock misery

[bjsteyn]

Any body with experience in stock updating from stock orders. I have tried a thew things , but my sulotion still feels to complex. I am sure there must be a easier way.

Basically when you order stock from a stock order form it must directly go to UnitsOnOrderCA (cases) and UnitsOnOrderSU (single units) and when the stock comes in and you enter it in the Stock Order Form UnitsInStockCA, UnitsInStockSU must be updated and UnitsOnOrderSU and UnitsOnOrderCA must be updated respectively.

But when the user changes something it must be updated again.

At the moment i'm storing the values before anything is changed and then
updating it after the change is complete.

For example :
UnitsOnOrderCA = UnitsOnOrderCA - (UnitsOrderedCAold - UnitsInCAold)
+ ((UnitsOrderedCAnew - UnitsInCAnew)

What is the best way to do it?

bj steyn

 

[pbaldy]

Many of us would suggest not trying to store the value. Nice discussion here:

http://allenbrowne.com/AppInventory.html

 

[bjsteyn]

Hey Pbaldy, I am currently writing a program for a company that stocks Koffee, chocolates etc. They sell mainly to Base,B&I,Hospitality,Wholesale customers. This is my first database. When u need to order stock from 'Nestle', the company he orders his stock from, it needs to go "on order" and when the stock comes in it should go into stock. Should i let him enter it into the "stock order form" and then manualy change stock on the "stock form".

 

[pbaldy]

Well, my first step in designing a new application is to lay it out on paper. Identify all the inputs and outputs involved in the application. From there organize it into properly normalized tables. Once you have properly designed tables, the forms and such sort of fall into place.

Since I don't have that info about your application, I can only guess. My guess would be that you need an "orders" table to track those orders, a "received" table for when the orders are received, and a "sales" table. Each of those may actually involve 2 tables; one for the master info and one for the detail (since each order/sale can probably involve multiple products). Current inventory for any given product would be the quantity received to date less the quantity sold.

A sales/inventory application is a big undertaking for your first database. They can get intricate and I think really require forethought in table design.

 

[bjsteyn]

Hey Paul, thanks. What is normilization? I'm guessing it is the way your tables relate to one another.

At the moment my stock is in my "Products" table.

Then I have a "stock orders" table, which stores:

"StockOrderNumber","OrderedFrom","StockOrderDate","ToBeDeliveredDate","DeliverDate"

and a "Stock Orders Detials" table, which stores:

"StockOrderNumber", "ProductID", "StockOrderedSU","StockOrderedCA","StockInSU","StockInCA"


Will go back to the drawing board as stock is an important part of this business. Didn't want to create to a stock table, but now I c I'm going to have to.

When I started this project I was trying to help a neighbour out who started up a franchise,the first of 21 to follow, that stocks 'Nestle' (The company he reports back to) products. B4 this customers had to wait 3 or 4 days for stock to arrive from 'Nestle'.

My neighbour is already using a version of my program.

One of the 'Nestle' bosses had a look at the program and now he wants to use it for all 21 franchises. This looks like it is turning into a big opportunity/project. I have taught myself VB6 and have been wanting to go into programming.

I will have to design a website for this business so customers can place orders online as well.

And then later on I will have to design one main DB for 'Nestle' that can import data from all the franchises.

It will take about 6 months before the other franchises starts kicking off.

So I have some time to improve my knowledge.

I have attached my current relationship. Can u take a look at it and c what u think.

If u take a look at my relationship you will c I have one products table and 2 price list tables. I am also wondering if I should have 2 product tables. I tried this but it didn't work in relation with my order details.


thanks bj

 

[bjsteyn]

Got this link from one off the other forums . Helped alot.

http://www.allenbrowne.com/AppInventory.html

 

[pbaldy]

You didn't see that I posted that link in my first reply?

 

[bjsteyn]

I actually did, i forgot, i checked it out briefly. when u told me to create the different stock tables, i looked for the link again and it made sense. Thanks. Did u check out my relation that i attached?