By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Inventory Control.. This is more COMPLEX than I thought!

P: n/a
Well,

I think I have bitten off a little more than I can chew (at least all
at once), and I'm only trying to hammer out tables/relationships at the
design level. Which translates to "Seasoned Access User needs expert
advice or opinion on how to solve what was initially thought to be
a fairly straightforward db design. I hope I can explain this in a way
that makes it fairly easy to follow. Note, I have performed a fairly
exhaustive search for inventory control examples and would consider
following a path similar to what Allen Browne has suggested at
(http://allenbrowne.com/AppInventory.html) however, this approach would
be further complicated by the following conditions or constraints.

I make finished products from raw materials and packaging components.
I will need to reduce some inventory items after finished goods are
produced.
However, the reduction of inventory occurs during 2 seperate processes.
1- The raw materials are compounded in a batch (tank(s)) according to
the formula values.
2- The finished batch is then (filled) placed into various containers
along with other packaging components (label, seal, cap, box,etc.) thus
creating a finished product which is new inv.item.
I will then need to add these newly made/converted products to the
inventory.
I have 1 large building that is divided by partitions or walls into 3
seperate areas.
( 1-Office, 2-Production, 3-Shipping & Receiving ) and need to track
inventory
quantities as they move from S&R to Production and back to S&R to be
shipped.
I have thought about incorporating a Bill of Materials type of system
using a BOM table into a design much like Allen Browne's... but every
time I try to draw it out and create test cases and tables, I seem to
run into roadblocks and fall flat on my face meanwhile simultaneously
pulling my hair out. Experts...Please Help!

Nov 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Sounds like you have a raw materials (RM) in and finished goods (FG) out
need.

All RM and FG are seen as products that reside in a warehouse of some
description. your system should be able to handle multiple warehouses.

Goods received get booked into a warehouse (this may just be a back room).

You will need a BOM for assembled finished goods products. In your case this
includes all materials including packaging etc,

You production functions should include a job system where you create a job
to make a specified quantity of a product. The BOM of the product is used to
determine what gets issued. Materials may get committed prior to issuing to
assist with managing stock. This ensures that when scheduling jobs you have
sufficient materials to do so. You can commit stock to jobs far in advance,
thus allowing you to see if more stock needs to be ordered for jobs.

At some point it is then issued to production to a Work In Progress (WIP)
warehouse for a specific job. Someone has to issue the stock to the job.
Keep track of who did it for obvious reasons. Create an Inventory
Transaction record in a table that has full details of the transaction,
where it came from, went to, quantities and values, and who did it, time
etc.

Movement from each stage may also include value adding so include this when
issuing from one area to the next, accumulating over the process.

At any time you can then query the database to find where all your stock is,
and its current value to you.

Eventually, you record production and it is reissued back to SOH with all
accumulated value adding as the FG product.

You should be able to issue and return stock from WIP, regardless of the
BOM, as mistakes get made and there are always special cases. Recording
production may not be the exact amount forecast so allow changes to the
amount actually produced.

I could go on for ages here but hope you get the idea. Happy to help at any
time.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
www.asken.com.au

"tHeRoBeRtMiTcHeLL" <tH***************@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
Well,

I think I have bitten off a little more than I can chew (at least all
at once), and I'm only trying to hammer out tables/relationships at the
design level. Which translates to "Seasoned Access User needs expert
advice or opinion on how to solve what was initially thought to be
a fairly straightforward db design. I hope I can explain this in a way
that makes it fairly easy to follow. Note, I have performed a fairly
exhaustive search for inventory control examples and would consider
following a path similar to what Allen Browne has suggested at
(http://allenbrowne.com/AppInventory.html) however, this approach would
be further complicated by the following conditions or constraints.

I make finished products from raw materials and packaging components.
I will need to reduce some inventory items after finished goods are
produced.
However, the reduction of inventory occurs during 2 seperate processes.
1- The raw materials are compounded in a batch (tank(s)) according to
the formula values.
2- The finished batch is then (filled) placed into various containers
along with other packaging components (label, seal, cap, box,etc.) thus
creating a finished product which is new inv.item.
I will then need to add these newly made/converted products to the
inventory.
I have 1 large building that is divided by partitions or walls into 3
seperate areas.
( 1-Office, 2-Production, 3-Shipping & Receiving ) and need to track
inventory
quantities as they move from S&R to Production and back to S&R to be
shipped.
I have thought about incorporating a Bill of Materials type of system
using a BOM table into a design much like Allen Browne's... but every
time I try to draw it out and create test cases and tables, I seem to
run into roadblocks and fall flat on my face meanwhile simultaneously
pulling my hair out. Experts...Please Help!

Nov 17 '05 #2

P: n/a

Inventory control systems are inherently complex, and they usually take months
or years to develop. Furhtermore, you are also talking about more than just
inventory control, you're talking about a miniature custom MRP application.

About the only hope you have of streamlining the process is to work with the
business people to engineer not just software, but a combination of software
and process that will meet all the needs and be reasonably simple to automate.
Bounce ideas off each other to get an idea what things work and are easy, and
what things don't work or are not worth the effort. Avoid feature creep like
the plague, and only implement in the computer what there is substantial value
to doing -in- the computer.

Things that need to be recorded along with core data, but are not part of core
requirements should not be normalized or validated up-front because it takes
too much time away from work on core requirements. If, for instance, you need
to track vendor addresses, but vendor information is not a core responsibility
of the application, don't normalize the address structure, just make a Memo
field for vendor address. Be ruthless about focusing attention on doing the
highest priority stuff well, and letting everything else be ad hoc.
On 16 Nov 2005 19:02:24 -0800, "tHeRoBeRtMiTcHeLL"
<tH***************@gmail.com> wrote:
Well,

I think I have bitten off a little more than I can chew (at least all
at once), and I'm only trying to hammer out tables/relationships at the
design level. Which translates to "Seasoned Access User needs expert
advice or opinion on how to solve what was initially thought to be
a fairly straightforward db design. I hope I can explain this in a way
that makes it fairly easy to follow. Note, I have performed a fairly
exhaustive search for inventory control examples and would consider
following a path similar to what Allen Browne has suggested at
(http://allenbrowne.com/AppInventory.html) however, this approach would
be further complicated by the following conditions or constraints.

I make finished products from raw materials and packaging components.
I will need to reduce some inventory items after finished goods are
produced.
However, the reduction of inventory occurs during 2 seperate processes.
1- The raw materials are compounded in a batch (tank(s)) according to
the formula values.
2- The finished batch is then (filled) placed into various containers
along with other packaging components (label, seal, cap, box,etc.) thus
creating a finished product which is new inv.item.
I will then need to add these newly made/converted products to the
inventory.
I have 1 large building that is divided by partitions or walls into 3
seperate areas.
( 1-Office, 2-Production, 3-Shipping & Receiving ) and need to track
inventory
quantities as they move from S&R to Production and back to S&R to be
shipped.
I have thought about incorporating a Bill of Materials type of system
using a BOM table into a design much like Allen Browne's... but every
time I try to draw it out and create test cases and tables, I seem to
run into roadblocks and fall flat on my face meanwhile simultaneously
pulling my hair out. Experts...Please Help!


Nov 17 '05 #3

P: n/a
These appear to be very thoughtful replies and well-rooted in perhaps
years of previous experience. My current thought process has in some
way taken into account a number of these issues, however, these ideas
have never occurred to me (at least not in my cerebral cortex) in such
a clear and concise manner. Thanks.

I would like to continue this post in a way which adds increasingly
more detail about these and many other IC/MRP design and development
issues as I encounter (and hopefully resolve) them. My hope is that one
day they might be of use to someone else out there (possibly banging
their head against a wall and searching for some answers or clues)
regardless of whether or not they may be design specific (my case),
access specific, or just common/general db issues when creating IC/MRP
systems.

I would also like to point out that I have no commercial interest in
pursuing this project other than the success of the system and its
effects on the efficiency, manageability, and ultimately the
scalability of our family business as we continue to grow.
Unfortunately, I have no team to which I can question or defer the many
problems and issues that will arise during the development process. Let
me elaborate. On a pure requirements basis, I believe I will be able to
call on some people for help and insight (my father has been
particularly helpful when he has set aside the time to do so). However,
being the only geek (read:computer guy) around here has brought about a
fairly frequent situation in which I find myself spending a great deal
of time having to explain some basic computer
and database concepts just to get to the point where I can explain the
real issue at hand. I even once made the mistake of trying to show
someone a little code and explain a programming issue. It led me to the
following conclusion (in the words of a wise-guy) ....

FORGETABOUTIT!

Nov 17 '05 #4

P: n/a
I have been working on this type of application, for a family business for
many years, starting before Access 2. May I make some suggestions.
1) I agree with a previous post to keep it simple.
2) Keep the data normalized, no mater how basic the current requirements. It
will be much easier to make changes for new requirements.
3) Get your users to document their work procedures, don't try to develop
their work procedures while automating. You can't possibly know how they do
their jobs, unless you have actually done it.
4) Release program updates in small increments, to allow you to verify your
changes.
5) Read and post to this newsgroup often.

"tHeRoBeRtMiTcHeLL" <tH***************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
These appear to be very thoughtful replies and well-rooted in perhaps
years of previous experience. My current thought process has in some
way taken into account a number of these issues, however, these ideas
have never occurred to me (at least not in my cerebral cortex) in such
a clear and concise manner. Thanks.

I would like to continue this post in a way which adds increasingly
more detail about these and many other IC/MRP design and development
issues as I encounter (and hopefully resolve) them. My hope is that one
day they might be of use to someone else out there (possibly banging
their head against a wall and searching for some answers or clues)
regardless of whether or not they may be design specific (my case),
access specific, or just common/general db issues when creating IC/MRP
systems.

I would also like to point out that I have no commercial interest in
pursuing this project other than the success of the system and its
effects on the efficiency, manageability, and ultimately the
scalability of our family business as we continue to grow.
Unfortunately, I have no team to which I can question or defer the many
problems and issues that will arise during the development process. Let
me elaborate. On a pure requirements basis, I believe I will be able to
call on some people for help and insight (my father has been
particularly helpful when he has set aside the time to do so). However,
being the only geek (read:computer guy) around here has brought about a
fairly frequent situation in which I find myself spending a great deal
of time having to explain some basic computer
and database concepts just to get to the point where I can explain the
real issue at hand. I even once made the mistake of trying to show
someone a little code and explain a programming issue. It led me to the
following conclusion (in the words of a wise-guy) ....

FORGETABOUTIT!

Nov 18 '05 #5

P: n/a
On Fri, 18 Nov 2005 10:45:43 -0600, "paii, Ron" <pa**@packairinc.com> wrote:
I have been working on this type of application, for a family business for
many years, starting before Access 2. May I make some suggestions.
1) I agree with a previous post to keep it simple.
2) Keep the data normalized, no mater how basic the current requirements. It
will be much easier to make changes for new requirements.
Here, I would just add a nuance. Keep the data normalized for everything tied
to the core requirements, and blow off the rest, because...

a) Normalizing everything will vastly increas the complexity of the schema and
interdependencies of the code components on non-core components, slowing
development, and increasing debugging time.
b) If non-core requirements become more important later, it is only -then-
that you'll know enough about the domain to properly define what "normalized"
means.

Also, keep in mind that it may be appropriate to a normalized database for
data entry/editing, and copy data to a data-mart style database for much of
the reporting. This uncouples normalization issues necessary for data
integrity from report query designs and makes the app more robust.
3) Get your users to document their work procedures, don't try to develop
their work procedures while automating. You can't possibly know how they do
their jobs, unless you have actually done it.
And be willing to suggest changes to their procedures. Even if they change
nothing, their explanations of why not will give you a deeper understanding of
the domain which will help you model it better.
4) Release program updates in small increments, to allow you to verify your
changes.
.... and this does not come naturally. Create processes to help you actually
do this. Prefer releasing on time over releasing with all the scheduled
features.
5) Read and post to this newsgroup often.


For sure :)
Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.