Question Creating Stock Control

Z

Zakes

New member
Local time
Today, 20:49
Joined
Nov 20, 2011
Messages
2
Dear People

I am stuck and need your help. I am busy creating a stock control database in which I want the application to automatically add an item when I enter it in the database and subtract it when I use it. I want to include the following tables and fileds:
Item (The name of that particular part)
Serial Number (Identifies that item)
Date Received (This adds quantity in stock)
Date Used (This subtracts quantity instock)
Site Name (Name of Customer)
I am using Access 2007.

Your help is highgly appreciated

Zakes:confused:
 
Last edited:
Dear RainLover

Thanks for your response I looked at it but honestly speaking, I do not understand where to start. I am not a programmer as I do not understand programming language I am a mere enduser. Maybe this forum is not for me. Can you create one such database I am asking assistance with?

Kind Regards

Zakes
 
Once you start creating a database you are really no longer just an end user but an application designer. I cannot imagine it would be possible to create an effective stock control database without using some code.

The learning curve involved in designing anything more than the simplest of databases is substantial. You probaly should just search for an inventory control database template.

You need to get a good grasp of database concepts, particularly normalization. Then you will be able to construct your tables. This is vital.

You should then be able to understand the concepts on the page RainLover linked even if you don't understandthe code. Basically it is saying what you have asked is the wrong question because one does not normally store the quantity on hand but rather calculate it from stock movements each time it is required.
 
Here is a Demo. I am not sure everything is correct but it may help point you in the right direction.

Galaxiom is correct in what he said.
 

Attachments

Zakes...

I would suggest that you have one main Table this will include the kind of fields that you have listed.

One field that I would have is Date Sold

Anything without a Date Sold is in Stock anything with a Date Sold is Sales.

You will NEVER actually delete records from stock you just change the value in your equivalent stock sold field then run a query to determine stock based on the value in this Stock Sold field (your field name may differ).

So by running queries off this one "Stock" table you can determine things like the stock at any particular point in time/ sales for a particular period / how much you bought during a period or what your stock is right now and lots of other things as well. (Pretty much anything you can think of in actual fact)

I've not looked at Rain lovers' database but I suspect its the same set up
 
Last edited:
Lightwave

I actually have Six (6) Tables. In addition, a Table for Product Description.

And Yes it is properly Normalised.

So nothing like what you have proposed.

Best have a look at the example.

Galaxiom is slightly incorrect in that from Time to Time one must do a Stock take and that Value must be stored. This is required due to Spoilage, Theft etc.

Image loading a truck with Rocks. How does one measure the exact amount sold. In fact how does one measure the exact amount of rock in a large pile that is not symmetrical
 
Last edited:
Ok just looked at Rainlover's design. Now here is where you get into the subtlety of design. Rainlover's design I would argue is targeted towards homogenious products.. Particularly dealing with homogenious materials rather than unique product stock control.

It treats one stock item as equal to the next ie one Duck is equal to the next Duck or 10kg flour is not different from another 10kg of flour.

I assumed when you had serial numbers that each individual product was totally unique and therefore when you sell a product your not necessarily selling one of a number but your selling a unique thing. In this case disaggregation of sales and acquisitions is probably not appropriate.

In a serial product stock position if you do a stock take you haven't lost 3 ducks you have lost stock serial item 2435 / 34565 and 3453

As for tables when I said one table I mean't one main table...
 
Last edited:
Lightwave.

You have demonstrated that there is more than one way to prepare the Haggis. I prefer mine with tomatoe sauce while others just have a wee dram.

So the OP needs to decide which recipe suits them best.

Thanks for your constructive views.
 
Last edited:
You must log in or register to reply here.

Similar threads

C
Inventory- hold stock level or not
Replies
11
Views
332
The_Doc_Man
The_Doc_Man
A
Simple warehouse data entry
Replies
12
Views
627
arnelgp
arnelgp
H
Solved Query for the balance stock material
Replies
8
Views
557
GPGeorge
GPGeorge
T
Creating stock balance by month
Replies
5
Views
566
mike60smart
mike60smart
C
Automated Production Planner
Replies
11
Views
695
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom