Requesting suggestions for manufacturing app...

J

Jim Beaubien

Hello All, I am currently working on an my first Access application and thought I would ask the group for suggestions about how best to implement certain features... The project is for a semiconductor manufacturer to track various lots through the various stages of the manufacturing process and inventory. So far it's relatively simple with one main table containing all known lots and a few other supporting tables for part numbers, shipments, etc. I was looking for suggestions on how to: 1) Implement "flows". There are a handful of different flows, each of which is basically an ordered list of steps or locations through which a given lot will travel. To make things a bit trickier, there needs to be the ability to have "subflows" as well. For example, a lot may have to go through a re-work flow before continuing on with the original flow. 2) Lot History. I need to be able to recall the entire history of any transactions involving a given lot. The problem is that even though each lot has a unique lot number, that lot number may change 3 or 4 times before the product is finished due to lot splits, combining lots, temporary lots, etc. Really not sure where to begin tackling this one. 3) Comments. Kind of similar to #2. Need to be able to add one or more short comments when necessary to individual lots. I guess I could just add a text/memo field to the lot table, but the comments will most likely be used very sparingly and so this seems like a huge waste. I'll just stop there for now. I appreciate any comments/suggestions that anyone might have. If I were to pick just one, I would say the lot history is what puzzles me most. Thank You, Jim Beaubien [email protected]
 
G

Guest

See comments below

Jim Beaubien said:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<pre>Hello All,

I am currently working on an my first Access application and
thought I would ask the group for suggestions about how best to
implement certain features...

The project is for a semiconductor manufacturer to track various
lots through the various stages of the manufacturing process and
inventory. So far it's relatively simple with one main table
containing all known lots and a few other supporting tables for part
numbers, shipments, etc.

I was looking for suggestions on how to:

1) Implement "flows". There are a handful of different flows, each of
which is basically an ordered list of steps or locations through which
a given lot will travel. To make things a bit trickier, there needs
to be the ability to have "subflows" as well. For example, a lot may
have to go through a re-work flow before continuing on with the
original flow.
Click to expand...

Create a "Flow" table that will have a record created each time a lot enters
a location in a flow. If there is a preordained order, then another table
that defines the flow order might be a good idea. This flow subsystem, it
itself, could be pretty complexe if needed. For example, some data items for
a location might be the previous location, the next location for normal
processing, and an exception next locaton. If there are more than one
possible exception locations, then another child table with possible next
locations may be in order.
2) Lot History. I need to be able to recall the entire history of any
transactions involving a given lot. The problem is that even though
each lot has a unique lot number, that lot number may change 3 or 4
times before the product is finished due to lot splits, combining
lots, temporary lots, etc. Really not sure where to begin tackling
this one.
Click to expand...

Using the Flow table above, you would be able to track the history of the
lot with no problem. As to changing lot numbers, that is easy. The internal
lot number should be an auto number field and the primary key of the Lot
table to which all descendant tables are related. Then you just have a field
in the Lot table to hold the Lot Number the humans need to see. If you need
to see a history of changing lot numbers, then we need another child table
with a record each time the Lot Number changes.
3) Comments. Kind of similar to #2. Need to be able to add one or
more short comments when necessary to individual lots. I guess I could
just add a text/memo field to the lot table, but the comments will
most likely be used very sparingly and so this seems like a huge waste.
Click to expand...

Issue all users a note pad and a pencil :)

Actually, if this will be used sparingly and the notes will always be
associated with the lot, then one comments field in the Lot table would do
just fine.
I'll just stop there for now. I appreciate any comments/suggestions
that anyone might have. If I were to pick just one, I would say the
lot history is what puzzles me most.
Click to expand...

The lot history and the flow are actually one and the same. As I stated
previously, add a record at each change of location and you can easily see
the history of the Lot.
 
G

Guest

1) Implement "flows". There are a handful of different flows, each of
which is basically an ordered list of steps or locations through which
a given lot will travel. To make things a bit trickier, there needs
to be the ability to have "subflows" as well. For example, a lot may
have to go through a re-work flow before continuing on with the
original flow.
Click to expand...

Use these tables. Relate through the ID's. I'm assuming that the subflows
can be linked to the LotFlowID, because that is already associated with both
the LotID and the MainFlowID.

tbl_Lots
LotID (PK)
LotIDInfo

tbl_LotFlows
LotFlowID (PK)
LotID
MainFlowID
LotFlowInfo

tbl_MainFlows
MainFlowID (PK)
MainFlowInfo

tbl_Steps
StepID (PK)
MainFlowID
StepInfo

tbl_SubFlows
SubFlowID (PK)
LotFlowID
SubFlowInfo
2) Lot History. I need to be able to recall the entire history of any
transactions involving a given lot. The problem is that even though
each lot has a unique lot number, that lot number may change 3 or 4
times before the product is finished due to lot splits, combining
lots, temporary lots, etc. Really not sure where to begin tackling
this one.
Click to expand...

Make a master table for similar lots, then use a linking table to show which
lots belong to the master lot. You might want to put a date field into the
linking table, tbl_AllLots, which can then be used for chronologically
showing which lots belong to the MasterLot.

tbl_MasterLots
MasterLotID (PK)
MasterLotInfo

tbl_AllLots
AllLotsID (PK)
LotID
MasterLotID
AllLotInfo
3) Comments. Kind of similar to #2. Need to be able to add one or
more short comments when necessary to individual lots. I guess I could
just add a text/memo field to the lot table, but the comments will
most likely be used very sparingly and so this seems like a huge waste.
Click to expand...

This is the easiest one. Just make a table that will only store comments on
particular lots that you need comments on.

tbl_Comments
CommentID (PK)
LotID
CommentText

This may need some tweaking, because you will have some details that I don't
know about. But gives you a general idea of how to connect stuff for the
information you need. Even though there is the comments table, you may want
to have some comment fields in most of the linking tables, just so you can
make notes to yourself on why you did things at a certain place. Nice little
problem. Your forms are going to be fun to put together . . .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parcel Tracking App 1
Windows XP Prohibit record run history 0
Duplicate data 4
Windows 10 Do you use the Windows App Store? 7
AV for Macs 12
Dsum Issues 0
Manufacturing Processes Database? 7
Things to do in Amsterdam 13

Top