Question Simple Guideline to move forward

[aces27]

Hi ,
I am trying to maintain the inventory of my factory.
I would like to have it simple and built up from scratch so that I can understand it well.

Currently I have two simple tables.

Production Table :
Sno-Date-Product ID-Quantity

Invoice Table
Sno-Date-InvoiceNumber-ProductID-Quantity-Rate-TaxValue

Note : Tax Value is always a fixed percentage based on the rate and quantity.
Can the form handle this sort of input to calculate automatically.

Two Questions :
How Do I have the below for each ProductID:
To have this - Should I use a Query. I would prefer a table.

Sno-date-Opening Balance-Manufactured-Dispatched-InvoiceNumber-InvoiceValue-ClosingBalance

One Invoice can have multiple products , On a single day there can be multiple products and multiple entry (shift manufacturing) per product.
Also How do I create flexibility specifically two purposes :
Choosing the date period and product for which the third tables data is to be fetched.

Please provide some guideline and if not too much of an effort a template.
I am eager to work and there is a whole lot more that I will have to do.
Just wanted to know which way to add / subtract / update values.
And another thing to have a date wise easily accessible information of the stock of all the product types on that date.

Please help.

Thanking you

Regards

Aces J

 

[spikepl]

Before you do anything else, read up on normalization http://en.wikipedia.org/wiki/Database_normalization or here http://support.microsoft.com/kb/283878

 

[jdraw]

There are some free data models dealing with inventory here

http://www.databaseanswers.org/data_models/index.htm look up Inventory

There are also some templates available from M$oft for Inventory control.
Also, the Northwind Traders sample db that comes with Access has some features re Inventory.

As Spikepl says read up on Normalization. Here's another link on Normalization, ER Diagramming etc. http://www.rogersaccesslibrary.com/forum/topic238.html

Strongly recommend the first few topics to get you started.

 

[Lightwave]

Congratulations on signing up. This is potentially a big project but as long as you can create some kind of labour saving incrementally no reason not to give it a go and try and develop your own system

Templates are available here
Inventory
http://www.accesstogo.org.uk/Inventory_Assets_Register.html

Simple Sales Inventory
http://www.accesstogo.org.uk/Simple_Sales_Inventory.html

From what I can tell you can get into the back of these and start to figure out how they are doing things.

Ie reverse engineer them to see how they do it..

Costs money but not much...

 

[aces27]

Hi ,
Thank you for your response.
Yes This may turn out to be a big project. If there are good inputs at the forum then this would be a helpful case for many. Existing Inventory systems though well made may not be very suitable or tailored well.

Let us work on two aspects for once :
1.How to get the Opening Balance / Closing Balance
2.Having a form to get the choice of inputs - product type - date range and
by default a means to provide ALLTIME or Current Month.

Next Step would be to have a table with raw material IN and automatically have raw material OUT based on product.

From the Experts a simple effort template would be very beneficial.

Thanks

Eager to hear more.

Aces J

 

[spikepl]

Work on one aspect first - the data structure.

Read up on Normalization. This is the groundwork for a good data structure, which in turn is the fundament of any database.

Then present some envisaged and hopefully normalized tables containing the information you think is relevant.

Once the structure of the data is determined, then one can discuss other things.