Small factory resource planning system

[Cindy]

I have written an application for a small factory (8 employees) which
tracks their inventory through shipments, purchase orders, and
miscellaneous adjustments for scrap, samples, lost parts, etc. I
demonstrated it for them yesterday, and they are very happy, and it
does exactly what they want it to do. But now they want to add
something, and I am not even sure it *can* be done, let alone how to
do it.

Basically what they want is to track each part at each stage of the
process. In other words, they have Part A which comes into the
building, then goes through process 1, process 2, process 3, and out
the door to the customer. They want to be able to look up Part A and
see how many are at each process. They have about 300 parts, 3
customers, 2 suppliers, and maybe 5 or 6 processes, so its not a lot
of information--and definately not enough for them to go out and spend
$20 or $30 thousand dollars on commercial software--so here I am. One
of the problems is that not all parts go through each process, and
some parts don't exist until they are assembled from other parts.

My thought is that to accomplish this would require basically 4
tables:

parts
operations
routing (which parts go where)
work in process (how many of each at each step)

Now I know that the work in process table goes against the standard
advice of not storing a calculated field, but I can't see any way
around this.

Has anyone ever done such a thing with Access? Is it even possible? Or
should I just say "sorry, Mr Factory Owner, you just can't get there
from here"?

Sorry about the long post, just trying to be clear. Thanks for any
insight you might have.

Cindy

 

[Larry Daugherty]

Hi Cindy,

Your tracking application can easily be done. Make sure that your tables
accurately reflect real world concepts. Don't know that I'd agree with the
names you've chosen. You'll need a junction table on tblProcess and
tblPart, call it tblProcessesParts. That will be the table that tracks
which parts are in which process.

HTH

 

[Cindy]

Hi Larry, thanks for the tips, and the assurance that this guy is not
asking for the impossible. The table names I used were not *actual*
table names (but I don't use the tbl prefix either), just names I
threw out there for clarity's sake. That said, I have a question about
your scenario, because I am unclear about something. tblProcessesParts
would indeed be the junction table between processes and parts, the
link in a many to many relationship. But I am not certain this is the
best place to also store the quantity information. Can you elaborate?

Thanks again,
Cindy

 

[Larry Daugherty]

You're welcome, Cindy.

Each entry in tblProcessesParts could be for a single instance of a part
assigned to that process OR tblProcessesParts could have a quantity field.
That junction table is the only logical place to put information that
relates to the current instance of this part in this process. I often have
junction tables with lots of fields.

Not to worry overmuch about naming conventions, its more important that you
observe one than what it happens to be. I use the Reddick naming
conventions for guidance but am only righteous to a couple of levels deep..

HTH