DataBase - An Inventory order and tracking db for hydraulic fittings

Asked By SBGFF

04-Feb-10 11:43 PM

I am starting a new db to track hydraulic fittings and inventory. I want to
do it right, from the start by having it normalized, and not quite sure what
I am doing.
My employer  and head office is use to the old part # of the fitting and
wants to use it for billing and organization on the parts shelves, but the
parts have to be ordered using a new # part # because they changed
suppliers. Yes they should use the new #'s but they do not want to and they
sign my pay cheque, so I have to try to keep everyone happy.
Each fitting comes in different sizes, and is going to have an old part #
and an order part #. I think it should have an inventory amount, a sold
amount for the month / year, and amount ordered for the month / year.
I am not sure how many tables I will need to accomplish this and what
fields I should have in each table.
Can you send me in the right direction

Thanks Blair

06-Feb-10 01:34 PM

Hi,

Microsoft has some templates on their web site.  Here is a link to their
Inventory Management Access Template:


http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT101426031033&av=ZAC000

And here is a link to all of their templates:

http://office.microsoft.com/en-us/templates/CT101426031033.aspx?av=ZAC000


Good luck

Dennis
--
Dennis

06-Feb-10 01:54 PM

SBGFF,

I am not sure how much experience you have in do this, but from your
comments, I am guesing not a lot.  But what you are undertaking is a BIG
project.


1.  The first thing you need to do define is EXACTLY what you want your
software to do.  You state you want have an inventory control system.  Then
you talk about billing and organization of the parts shelf.  From our
description, I am not sure the "scope" or "things your project is to do" has
been defined.

2..  The second thing you need to do is identify all of the data fields that
you will need.  The easiest way to do this is to look at all of your existing
forms and reports.  You will need to have at least that much data.

3.  As you are building your list, you need to identify if the data item is
a true data field or a calculated field.  The price per item and number of
items is a data field.  The total cost is a calculated field (price per item
* number of items).

Regarding normalization, there have been books written about that subject.
It is impossible to address that issue in a e-mail.  However, here as some
links that might help you.

http://allenbrowne.com/casu-06.html
http://www.phlonx.com/resources/nf3/
http://www.blueclaw-db.com/database_3rd_normal_form.htm

I would suggest you do a google search on "Third normal form".

If you want, you can post all of your data fields here and I will try to
group them in 3rd normal form for you.

Good luck you have a huge task ahead of you.



--
Dennis

06-Feb-10 11:24 PM

Thanks for your help and suggestions on how to start, and yes I do not have
much experience, just enough to know to start with the right table setup
from the start, it makes things easier down the road

Thanks again
Blair

07-Feb-10 09:38 AM

been sleeping on it, and I do not do billing, I am looking after inventory
ordering and stocking. I use and make the hoses. I really only need
something to tell me what I got and need to order. Is that simpler to do or
is it still very complicated
Thanks Blair

08-Feb-10 09:08 AM

Ditto what everyone else said, most importantly about defining your entities.

Plus you have a gorilla in the living that you have not addressed.

In your planned real-world implementation of this, what "act" will cause
updates of your inventory.

If you ar thinking that the transactions themselves (purchases, sales etc.)
will automatically update your inventory, then you are tackling somethign
which both in the DB and in the real-world process changes is 20 times bigger
than you think.   In that case, the transactions (at the level of the
individual part number) will also be entities.

Much simpler would be to update the inventory numbers directly/manually.

08-Feb-10 08:26 PM

Blair,

How much programming experience do you have?  If you do not any programming
experience, and no database experience, you have a very loooong road ahead of
you.  Please do not let me discourage you.  There is a Chinese proverb that
state "A trip of 10,000 miles starts with the first step".  I just want you
to be aware of what is ahead of you.

I have been programming mid-range computers using relational databases for
over 20 years.  Then I got to Access and I have had a huge learning curve, I have
been at it part time for about a year and am still in learning process.

That being said, even given the huge learning curve, Access does simplify
developing database.  It makes it very easy to do some very nice things.

Before you even worry about Access and whether or not you can do this
project, you have to define the project.  From reading your response, I am not
quite sure you have done that yet.  A project definition can be a couple of
paragraphs to multiple pages long.  Yes, you need to be that specific.

In this, you need to specifically define that it is you want to do.  You can
not say we want to "look after inventory ordering and stocking".   You need
to be much more specific.  Below are some SIMPLE questions that need to be
answered before you can event start to think about doing this project.  It is
by no means a complete list.  These are just a FEW questions!

How will you maintain the ordering and stocking level?  Do you want to data
entry screen to record the amount on order as order are issued to your
suppliers or are you going to manually maintain the amount on order number?
How are you going to handle receiving amount less that ore more that what you
ordered.  How are you going to handle items on back order from your vendors?
How will you handle having multiple vendors for a part (and therefore
multiple order part numbers and potentially multiple prices?

Will you have a minimum on quantity on hand amount that if the inventory
drop below this point, it will appear on a report.  Will you have a maximum
order amount so that you can not accidentally order too much?

How will you record how much inventory was used today?  How will you record
how much inventory was returned today?  (Sometime stuff goes out, but is then
returned back to inventory - kind of like customer returns.)  What will be
internal procedures to make sure returned items are posted back to the proper
inventory item.  How will you take inventory the very first time?

These are just a FEW of the questions you need to asked and have answered
before you even beginning starting this project.


Again, I would strongly suggest you look at the links to the MS Inventory
template I provide above.  You can download it for free.  It is a complete
Access database with the tables, input screens (forms), the programming
behind each form (code), and queries.

Maybe once you study that example, you might be in a better position to
start defining what you want.

One other thing you might consider and that is the TIME it will take you to
build something versus buying an existing inventory control product.


--


Dennis

08-Feb-10 08:33 PM

Blair,

I found one other link for you.  Try this one:

http://ezinearticles.com/?Quick-Microsoft-Access-Course---Creating-an-Inventory-Control-Database-in-8-Simple-Steps&id=876751


Also, if you decide to proceed with doing it yourself, please let me know.
I have a business requirement that I did for my brothers retail store.  If
you want, I will be glad to send you a copy so that you can read it and
hopefully give you some idea where to start.

Good luck.  I sympathize for you, I have been there before.

Dennis
--

Dennis

08-Feb-10 08:40 PM

Blair,

You might want to do a google search for "microsoft" & "inventory control".
You might find some interesting stuff out there.

One other question, would you want to use bar code scanners and or print UPC
bar code?

Just so many question. Please do not let me discourage you.  Just want you to
know this is a big project for your first project.  But the inventory control
template should be a big help.

Dennis.