Stock Management System

[mis]

New Member

Hi to all members,

I need to create a management information system for a local company. I have met up with the company owner to see how the current system works and what he would like from this new system, The company has currently five stores, each store has a basic electric till, the stores keep a record of items sold by taken down the codes on the clothes, bags etc and then fax though the information every week, then the information gets updated to their stock control system in there main store.

I have taken this into consideration and have come up with the following;

The suppliers will have there information stored into the database system, some of the information that will be a numeric code to be identified on the system, there business name, a contact name and telephone number, fax number, email address etc.

For the database system to be able to monitor the stock travelling though the business a business database will need to be held. This database will need to store product information, a product code, the product name, product description, the supplier, the quantity of stock, price of stock etc. With this information the database system will be able to monitor stock entering the business and stock exiting the business, it will also be able to monitor existing stock and know when stock levels are low and when stock will need to be re-ordered, also with five locations of the business the stock location will also need to be monitored.

The business will need to know what stock has been re-ordered so that, the business knows what stock they are expecting and the quantity on order. The information needed to be stored for this section will be a order number, the supplier number, the stock number and the quantity of the items ordered.

The database system will also need to record customer information if items are returned; the information held for this will be customer name, customer postcode, customer house address.

From this the following tables have been set could someone let me know if i have missed anything, or if i am doing something which is not possible etc thanks

TABLE

Primary key

Product_tbl
UPC
Product code
Colour code
Quantity
Re-order
StoreID
SupplierID
BoughtPrice
SalePrice

Supplier_tbl
Supplier ID
CompanyName
ConFName
ConSName
ConMob
ComTel
ComFax
ConEmail
BAddress
BAddress2
BTown
BPostCode

Store_tbl
Store ID
SName
SAddress
SAddress2
SPostCode
STelNo

There is what i have done so far be very greatful for any feedback, I am new to this

 

[Tay]

Hello

Firstly, you may need more address fields than you already have. Secondly, I'd remove the spaces in between names such as 'product code' - it will make your life easier later on.
You will also need to think about how products relate to a supplier. I am assuming that one supplier can have more than one product, and that one product can come from more than one supplier?
I hope this is of some help to you - I'm sure others will give you more advice.

 

[mis]

Thanks

Thanks Tay,

What fields are you thinking of for the address fields then ?

The gaps between the names will not be done when enterying them into the tables.

I am thinking of when the products get entered into the system; the user states what supplier it came from. (is there something that i am not seeing?)

I was thinking off Incorporating a facility so that the company can produce packing listes stating what has been sent in each box and where is it going to, so when the shop recieves it they can check the box against the packing list. NOT SURE HOW TO ACHIEVE THIS anyone ?

 

[Mile-O]

You are missing the important table that links it all together. A table for orders.

 

[mis]

Order Table

What would the order table be holding? can you put some examples in

 

[Mile-O]

Re: Order Table

What would the order table be holding?

Details about the order, for example.

can you put some examples in

Have you seen the Northwind database that comes with Access? That will offer the best example for what you want.

If you haven't got it then you can always do a search for it and download it from MS Knowledge Base

 

[mis]

Cheers

Thanks Mile-O-Phile,

Will find out how they order from other companies and will come back. Any other tips or ideas ?

 

[Mile-O]

Re: Cheers

Any other tips or ideas ?

Just one at the moment. the Primary Key for the products table above is UPC. I don't know what this is. If it's relevant, Index it as a field to Yes (No Duplicates) but leave your Primary Key as an autonumber like: ProductID

 

[mis]

UPC

The UPC is the barcode which you see on item when sold in shops. I choose this as it is unique for each product and if the company incorates a till system in the future they can use this field for selling items.

 

[neileg]

Mmm...

Be aware that a stock control system is a pretty complex application. I guess that you have some Access experience, you'll need it.

Are you expecting to hold the stock on hand in the product table? This is very bad practice. You should calculate the stock from goods in, goods sold and adjustments.

How are you going to value your stock? Accountants like FIFO (first in first out). This assumes that the oldest stock is sold first. This is pretty hard to implement in a database. Average price is somewhat easier and latest invoice price is the easiest, but also the least 'pure'.

Good luck

 

[mis]

neileg

I am understanding how hard it is but I have no choice and it is part of my learning curve. I do not know what you are refering to with you queries so if you could explain and offer more help be very greatfull.

 

[neileg]

It is possible to hold your stock balances as a single field in your product table. You can then update this field for stock movements perhaps using an update query. The major problem with this approach is that you need to ensure that these update queries run exactly once. If they run twice or not at all, the balance will be wrong. It is also difficult to see the audit trail this way, so if the balance is wrong, you can't see why.

My approach would be to use four tables. One to hold opening balances, one for stock receipts, one for stock issues (or sales) and one for adjustments. Any time you need the balance you form a query based on these tables. It is possible to combine these four into fewer tables, even down to one, but I prefer the four table setup. This gives a good audit trail, and the data is availble for other purposes, too.

 

[mis]

To neileg,

Could you please give me an example og a possible table designs based on the information you have given and I have given at the beginning.

 

[neileg]

Try these. I've ignored ordering both for purchases and sales and assumed straight cash purchases. This is plenty for you to get started with, I think. I hope by now the size of the task is dawning on you. You can buy commercial software that handles all of this sort of stuff for a realtively small amount...

tblProduct
UPC PK
Product code
Colour code
Re-order

tblSupplier
SupplierID PK autonumber
CompanyName
ConFName
ConSName
ConMob
ComTel
ComFax
ConEmail
BAddress
BAddress2
BTown
BPostCode

tblStore
StoreID PK autonumber
SName
SAddress
SAddress2
SPostCode
STelNo

tblStockBals
StockID PK autonumber
UPC FK
StockDate
StockQty
StockPrice

tblProdPurchases
PchID PK autonumber
UPC FK
SupplierID FK
PchQty
PchPrice
PchDate

tblProdSales
SaleID PK autonumber
StoreID FK
UPC FK
SaleQty
SalePrice
SaleDate

tblStockAdj
StkAdjID PK autonumber
UPC FK
AdjQty
AdjPrice
AdjDate

 

[mis]

New Year

Hi All,

Happy New Year and I hope everyone had a good Christmas to.

neileg - Thanks for the reply, could you explain what the usage would be for the StockAdj tbl, StockBal tbl

Cheers

 

[neileg]

Happy New Year, too.

Whatever system you devise, the reality of stock management dictates that stock levels diverge from what the system tells you. You need to have a way of adjusting the levels in the system to match reality. That is the purpose of tblStockAdj.

Unless you are starting with a brand new stores with nothing in stock already, you will have some opening balances. That is the purpose of tblStockBals. You might refresh this table when you want to ditch your transactions say at the start of a new year.

 

[joe90]

Hi Neil,

I have been reading many of yor replies and they have been very helpful, so firstly thank you for your kindness. Its very nice.

I posted a thread and thought it would be great if you personally could help me. I think I have bitten a little more than I can chew with this one.

really hope you can help me.

heres the thread...... ty

------------------

Firstly can I thank you for reading this and helping in anyway you can.
I have 2 tables (below), I am trying to find the best/correct way to return the desired results. I work for a small printers . I have been set a task to create (what I thought would be relatively simple) a database that will show what paper we are using on what jobs etc. This should be straight forward (I thought) as we only have a handful of different stocks/paper that we use and purchase a similar amount each month.
We get a delivery of paper, say a pallet with 18000 sheets, which could be used on numerous jobs. The process would be: on delivery of any stock it is labelled with the PO_Number, and the database is updated of what has been delivered to us. Then, as and when any operator uses the stock they would simply look at the label, and enter to the DB how much they have used, job number, stock type, size, and the PO Number and GSM(Weight) being the important ones. . What I would like is to create a report showing what quantity is remaining of any stock type, and what it had been used on? Something like this.
I have 2 tables as below.
IN_TBL
IN_ ID
DATE_OF_ORDER
QUANTITY_IN
SHEET_SIZE
GSM
PO_IN

OUT_TBL
OUT_ID
DATE_OF_USAGE
QUANTITY_USED
JOB_NUMBER
PO_OUT
PO_IN
I am confused as to if I should try and create queries and relationships to retrieve the results or to do a calculation within the report fields somehow.
Again thank you for any advice you can give. It really is much appreciated.
Jo

 

[Boro]

Don't know what is Your experience with data bases and Access, but I should say that I have done simmilar data base with just one store and that it took me a lot of time. It is difficult to say how much because I didn't do it continouslly. It took me maby 800-1000 working hours and that was not my first data base. Don't know how much tables I have but think that it's 8-10.

 

[neileg]

Ok, let’s have a look.

A couple of questions first. If you have a range of standard papers then you should have a table that identifies these. Is that the case?
Does it really matter which PO relates to the paper where you use it? I suspect not if your paper is standard then one batch will be interchangeable with another.

I suggest then that you need a table for paper, e.g:
tblPaper
PaperID autonumber PK
SheetSize
GSM
Colour
etc

A table for movements in stock, in or out
tblPaperMovements
MovementID autonumber PK
PaperID FK link to tblPaper
TransactionDate
Quantity Numeric, positive for incoming, negative for usage
MovementTypeID FK link to tblMovementType
POID FK Link to tblPurchaseOrders
JobID FK link to tblJobs

Movement types
MovementTypeID autonumber PK
Description – text Purchase, Usage, Scrap, Adjustment, Opening Balance, etc

A jobs table
tblJobs
JobID Autonumber PK
JobNumber
etc

A totals query on tblPaperMovements will give you the current stock. If you query using the PaperTypeID and MovementTypeID you’ll get details of purchase, usage, scrappage, etc.

Hope this gets you started.

 

[joe90]

Thankyou for your reply. Much Appreciated

I have created the tables and relationships you suggested but am a bit confused as to how it works. I have taken the liberty of sending you what I have done so far for you to see if I have understood correctly.

I am starting to see how complex this will be now to be honest. I am sure I can get my ead around it eventually, so please be patient with my responses and what may seem like silly questions.
Thanks again for all your help

Joe