Stock control table Normilization

[daniel aristidou]

Hi guys......
I'm using vb08 to make a database how ever since the back end will be access (07) i have decided to post my question here since its more relevant.
Im making my first stock control database.....and i need to record what is taken out and what is put in..... and the current

The basic fields i want to use are:
Product
Date
Amount

I dont know how to make the relation ships and how to auto generate the instock value for the current.

Could someone please help me

[sierra7]

Hi Daniel
This can become a fairly major assignment! You need to tighten up on your specification.

I should start by looking at the template application that ships with Access called "Inventory Control"

For it's complexity it only tackles one third of your objectives i.e. it raises Purchase orders for Products on Suppliers who then deliver to stock.

But it doesn't cover Sales orders to Customers and I can't see how you view current stock levels, although it may be there somewhere. (I just had a thought that it probably updates the Products table when goods arrive, but it doesn't so goodness knows !)

Even if you don't want a full blown PO/SO function you will still want some 'documented' method of adding/subtracting from stock, so the table relations will be similar even if the field names are different.

Its a start. Good luck.

S7

[daniel aristidou]

Hi... thanks for the repply...i don't need a very big complicated database it dosent need to track sale orders etc...my aim is to simply keep levels of stock within a warehouse ......there are of course multiple suppliers ,however there is only one extractor.

I thought Three tables would be neccesary:

1. Stock input
2. Stock level (and general information) e.g value
3. Stock output

My main concern is controlling the stock level using the other two tables...... I'm uncertain on how to do this.

I believe my intention was not as complicated as i made it seem before

[sierra7]

Hi again Daniel
You could certainly create a system as you described.

You need to create the notion of a 'Product' or whatever you are stocking, with a unique ProductID code for the system (most would use an auto-number field for this) You could then add all the other attributes you need, including cost (value) if you wished. You might want to keep TotalAvailable at this level too. It's usual to have some indication of Re-Order level too (plus lead time?) if you want to produce reports.

However, cost (value) varies over time and you may have to think how you are going to update it.

Your Stock Input document will need ProductID to tie in with Products, and Quantity. You may hold cost here and update [Products.Cost] as you add the new quantity, so value keeps current, although accountants usually don't like you to do this.
Additional fields might be Source, Date of arrival, name of person adding the data. Once the data is added you might want to disable/hide the 'Add' button, so an additional Boolean field 'StockAdded' would be the simplest way to stop adding stock twice without separate forms.

For 'normalization' you should consider an additional table for 'Sources' and link them to Input via a SourceID field.

Similar holds for Stock Out. You won't want Source(ID) on that form but it may be useful to include TotalAvailable, and a check routine so that you can't supply items which are out of stock.

You will need three single-record forms, to create Products and manage the Input and Output as above. A continuous multi-record form based on Products would allow you to scroll through stock. Multi-record forms based on Input or Output would allow you to monitor transaction by date etc. Depends what you want.

This is all so easy in Access that I would not contemplate VB.

The advantage of updating Quantity on the Products table is that you could purge Input or Output without worrying about stock levels. You may need to configure Input to allow negative quantities for 'adjustments' after Stock Take.

A more sophisticated approach is where Stock Level = sum of Input minus sum of Output but I think this is beyond what you are considering at the moment.

Hope this has helped.

S7

[daniel aristidou]

Hi again Daniel
This is all so easy in Access that I would not contemplate VB.

Reason im gonna use vb is because the user has very liitle computer knowledge also i want the experience......
i am a one man (Kid) team so i think it'll take a while

The cost.... do you think i should create an average cost by doin as you said and having cost in the input then working out the average....
i was also thinking of working out profit made....and possible profit......
by inserting a current selling price form....Though i think this would require alot of code..

The advantage of updating Quantity on the Products table is that you could purge Input or Output without worrying about stock levels. You may need to configure Input to allow negative quantities for 'adjustments' after Stock Take.

A more sophisticated approach is where Stock Level = sum of Input minus sum of Output but I think this is beyond what you are considering at the moment.

I originally thought of using the second....
Could you explain the first ?
Ill post back with my schema in a couple of days...

[sierra7]

Hi Daniel

The reason I would look at Access is that this is purely a database application. Not wishing to boast, I could have put it together quicker than the time it has taken me to write so far!

Once you have designed your tables you can click on a Wizard to generate the forms. You can adjust the default layout, I find, more easily than in VB. (Last time I looked I found re-sizing and aligning box a bit of a pain in VB if you didn't want them to click to grid) You only have half-a-dozen fields so even if you hate the layout of the Wizard, there will not be much to change.

I would certainly recommend Access as a prototype to help you get your design and functionality sorted out. Once you had a working model you could re-design it in VB (and see how much extra time it took)

As I recollect, there is not an After_Update event in VB and without that I would be lost. The combo box in Access is far more useful, but this is detail and I am biased.

COST (VALUE) - You really need to think about this yourself and what you want to achieve. Why not hold it twice, once on the Input and then update the current price on Product, so you can make current valuation of the stock but keep history of prices to graph trends

You have now introduced the notion of profit but were not using Sales orders and only had one 'extractor' so what are you dealing with? Are you dealing with an internal stores arrangement? It usually helps to understand and not make stupid suggestions!

Yes you can hold a target selling price on Products but often this is just a percentage uplift on Buy price. This is your (or your uses's) decision and a part of your design specification. You may also need to indicate Location of stock within the warehouse; is a particular item always located in the same rack/bin etc? These are the types of questions you need to ask of your user and build into your design.

CURRENT STOCK LEVEL - It's far easier to increment or decrement a value on the Products table each time goods arrive or are extracted, than having to sum-up everytime you want to see the stock level. Do both and try it! What do you want to do about Starting Stock? Is a new delivery of goods mixed with existing stock or is Batch Traceability required?

This is a part of your design and you must decide what is best for your (customer's) needs.

Best of luck
S7

[mshmyob]

Oops. I had read the first few posts and was about to give you a simple schema to track inventory but now you have thrown a wrench into the works by wanting to track selling price, purchase prices and profit margins.

As S7 says you better post ALL your requirements then we can do the design once.

You need to indicate all your business rules. The business rules will determine how to design the database structure. A simple example in your request is the following:

You say you have vendors but we need to know if EACH Product comes from MANY Vendors or does EACH Product come from ONE Vendor. I know it sounds like a simple variation but that slight difference will entail putting in a bridge table or no bridge table.

Also need stuff like does Selling price change over time and do you need to track that for reporting/history purposes etc.

Give us all your requirements and I am sure we can come up with a design.

Reason im gonna use vb is because the user has very liitle computer knowledge also i want the experience......
i am a one man (Kid) team so i think it'll take a while

The cost.... do you think i should create an average cost by doin as you said and having cost in the input then working out the average....
i was also thinking of working out profit made....and possible profit......
by inserting a current selling price form....Though i think this would require alot of code..

I originally thought of using the second....
Could you explain the first ?
Ill post back with my schema in a couple of days...

[daniel aristidou]

Thanks..im sorry ....i'v never done shema ect before.
There is one vendor for each product.
Traking of price changes is one of my....plans

I would like do what i stated in the previous post and put a buy price in to the input form and calculate an average price from there... the idea originated after i had a reply to the first post...

Also im uncertain about the design because i need to create it completely from scratch....i mean to say the company does not already have manual procedures because its new. so i have to think it all up by myself... luckily i have a close relation with the company.

[mshmyob]

I will work up a design and post it for you but you really should consider S7's suggestion of using Access for this. Your situation is taylor made for a RDMS, and using Access since you will be doing alot of VBA coding you will still get alot of VB experience (it is mostly the same) but will have the added benefit of the power of Access to manipulate your data and reporting, etc.

Many years ago I wrote an Order Entry app using VB5 as the front end and Access as the backend and just this year (2007) rewrote the entire thing with an Access 2007 front end and a MySQL backend. Trust me when I say it was so much better to write it in Access/VBA and not VB. Take S7's advice.

Thanks..im sorry ....i'v never done shema ect before.
There is one vendor for each product.
Traking of price changes is one of my....plans

I would like do what i stated in the previous post and put a buy price in to the input form and calculate an average price from there... the idea originated after i had a reply to the first post...

Also im uncertain about the design because i need to create it completely from scratch....i mean to say the company does not already have manual procedures because its new. so i have to think it all up by myself... luckily i have a close relation with the company.

[sierra7]

Daniel
The attached Access app has elements to help you build on. I would not normally do this but you are obviously struggling to start and this should help you kick-off.

It follows along the lines I have discused so far, with the Products table being updated with new additions and prices.

Rudimentary look-up tables are used for Employee and Suppliers.

I've done a basic 'Add Items' form but leave you to develop an 'Extract Items' form; it could follow similar lines.

One feature of the 'Add' form is that you can only add the quantity once.(although I dont guarantee you can't break it) You will also see that after addition the fields are locked, so you can't 'inadvertently ' change details of the stock. The field change colour to show locked or edit (add)

There is a read-only Stock browse screen and I've added buttons to help you get into the other screens.

It's just a start but gives some idea of what you can rapidly achieve with Access.

Best of luck
S7

[daniel aristidou]

This is great thanks..
Ill use this....however i'm still gonna try and pull it off with vb front end....if it fails or becomes too time consuming i'll revert to acess from end

[sierra7]

That kool. It was only intended to get you started.

The next stage is to get some user feed-back. How do they want to use it?
Can the products you are stocking be classified in some manner? Right now they are displayed ordered by an internal system number but they could be sorted by description or whatever you wanted. (Modify the query in the RecordSource for the form and in the Description column, set Sort = Ascending)

(Oops! Sorry I did not base the Stock Listing on a query in my hurry. That would be a good exercise for you!. After clicking in the right hand side of the line for RecordSource, Access will ask if you want to base the form on a Query. Say 'Yes' and the Query Design grid will open. Double click each of the fields in the box (Table) in the top half of the screen so they appear in the grid below. Then you can set the 'Sort' order etc. If you miss out any fields, other than Comment, they will appear as #Error# in your form)

Do you have your own part numbering system and is this the same or different from your Suppliers? You can add new fields as required to the table in design mode, then modify your query and form layout.

Access has in-built filtering using the right mouse button but there is also 'Filter by Form' using icons in the tool-bar at the top of the screen. You will need to add some new records before you can see how effective they are.

But, if you are only going to search by PartNumber you can add a control in the header of the form, to simplify operations for the user, then remove(hide) all unneccessary features.

Good luck

S7

[sierra7]

Daniel
I was thinking about your job over lunch and how you would get a 'valuation' of stock. Accountants generally want to value the stock at 'what you paid' for it, not what it's worth now and the schema I've used in db1 will need to be modified.

For accurate valuation you generally have to have some form of Batch Control on your products but I realised that you can approximate this by modifying the query on which the stock screen is based. Right now it used one line per stock item and that record is updated but the AddItem form. If we change the query we can display one line for each stock addition (batch). So if one item is recieved in two subsquent occasions, there would be two lines for it. When you 'picked' the stock you would have to decide which line (batch) you were picking from, then when that remaining quantity reached zero, no longer display the line. The 'Extract' form I left you to design would have to subtract the quantity from the 'AddItems' table and not (neccessarily) reduce the CurrentTotal on Products.

This will be achievable by pasting the following SQL into the RowSource for the screen (Click on Rowsource, open the query in Design mode, then click View in the top menu and choose SQL View, then paste the following)

SELECT Products.ProdID, Products.PartNumber, Products.Description, Products.LocAisle, Products.LocRack, Products.LocBin, Products.ReOrderLevel, Products.Comments, AddItems.AddQty AS currentQty, AddItems.BuyPrice AS Pvalue, AddItems.AddID
FROM Products RIGHT JOIN AddItems ON Products.ProdID = AddItems.ProdID
WHERE (((AddItems.AddQty)>0));

Close and save the revised query, cloise and save the form.

Your 'Extract' form must be designed to reduce the quantity on the record identified by AddItem.AddID

The stock 'value' can be calculated by multiplying remaining items by their buy-price. Those lines which have been 'used' will have zero quantity, hence zero value.

However, the down side of this fix is that only items currently in stock will be visible on the frmStock screen. Anything which is out of stock will not appear, even when there is already a Product record created for it.

This can be remedied using a different query for frmStock but it is somewhat more complicated and based on two queries. First create a new query;(Queries > New > Design View) don't add any tables but switch straight to SQL View, as above. Then paste in the following.

SELECT AddItems.AddID, AddItems.ProdID, AddItems.AddQty, AddItems.BuyPrice
FROM AddItems
WHERE (((AddItems.AddQty)>0));

Then save it as qryAvailableStock.

Now open up the RecordSource for frmStock and paste in the following;-

SELECT Products.ProdID, Products.PartNumber, Products.Description, Products.LocAisle, Products.LocRack, Products.LocBin, Products.ReOrderLevel, qryAvailableStock.AddID, qryAvailableStock.AddQty AS currentQty, qryAvailableStock.BuyPrice AS Pvalue
FROM Products LEFT JOIN qryAvailableStock ON Products.ProdID = qryAvailableStock.ProdID;

This is a query based on Products joined to qryAvailableStock and will produce a line for every item even when there is no stock. However, if there are two or more batches of stock it will return lines for each, again each will be identified by AddItem.AddID.

If you get stuck I will post a revised db but you ought to get the practice of doing these manipulations yourself.

S7

[daniel aristidou]

Thanks don't worry i understood what you said......i do have experience in creating access databses ...just not designing them solo...

Greatest thanks Daniel