Tracking workflow

B

BruceM

I have not received a reply to a question I asked a few days ago, so this is
something of a repost. I have tried to simplify, but there is still sort of
a lot to explain.

Let's say that engine parts are processed through up to three operations:
machining, sand blasting, and coating. Some parts receive all three
operations; others receive one or two, in any combination. For each job a
job number, the process plan (the step-by-step description of the work to be
performed), and the quantity is recorded. If 100 parts are machined and
arrive at the blasting operation, where it is discovered that 10 parts need
additional machining (rework), that number needs to be recorded, and a 90%
acceptance rate is calculated. If the same thing happens at coating, 90% *
90% = 81%. If all of the parts are OK, no number is recorded, and the
percentage is calculated at 100%.
The structure:

tblProcessPlan
PlanID (PK)
PlanNumber

tblOperation
OpID (PK)
OpDescription

tblPlanOp (junction table)
PlanOpID (PK)
PlanID (FK)
OpID (FK)

There is also a parts table:

tblPart
PartID (PK)
PartNumber
PartDescription

tblPlanParts (junction table for the parts in a particular ProcessPlan)
PlanPartID
PartID (FK)
PlanID (FK)

Forms bound to the tables are the means by which information is entered.
The main form is bound to tblProcessPlan. Subforms are bound to the two
junction tables. The ProcessPlan form will show a listing of operations,
and a listing of parts.

The information in the tables listed above is entered before the work is
done. The active record is created in another table, tblOrders, when the
job arrives at the plant for processing. This record includes the Order
Number, the ProcessPlanID, and the date. When the record is created,
selecting the ProcessPlan will show the ProcessPlan record from
tblProcessPlan, along with the Operation and Part information from the
junction tables. It will be something like this:

tblOrder
OrderID (PK)
Quantity
PlanID (FK from tblProcessPlan)
OrderDate

tblOrderOps
OrderOpID (PK)
OrderID (FK)
ReworkQty (number of parts reworked)

First question is how does the person creating the record select a
particular part number? A ProcessPlan may be for several similar parts, but
an Order is for just one PartNumber. I see how the ProcessPlan can be
selected, which could show Operations and Parts, but how to select a
particular part?

The other question is about the interface the operators will use after the
Order record is created. The most intuitive approach, I think, is to have
something like this as the physical appearance of the form in which the
operators record rework numbers.

Order Number: 00012
Part Number: 307
Number of parts: 100
ProcessPlan: 06-22
Op. #10: Machining
Op. #20: Blasting 10
Op. #30: Coating
Op. #40: Shipping

The 10 next to Blasting is the ten parts that were sent back from the
Coating operation for more work. The other operations had no problems.

The operators will be presented with a list of active jobs. If the work
order is for JobNumber 00012, in case of a rework problem they will select
00012 from the list of active jobs, and will be presented with the form
sketched out above. They will click next to Blasting (or whatever
operation) and enter the number.

Another possibility is that they will select the operation from a combo box
or list box in the OrderOps subform. The row source would be the same as
the record source for the PlanOp subform. They will select the operation
and enter the number of parts. I think this would work, but having the full
list with a space for entering the number of parts (sort of emulating a
paper form) would be preferable.

I'm looking for ideas about how I could best implement this in such a way
that computer-adverse operators have as little reason as possible for
complaint. They will complain anyhow, but will not be able to claim that
selecting the job number and entering the number of parts in the appropriate
line is too complex.

I can explain more as needed, but am trying not to drive people away from
the initial question.
 
B

BruceM

I think I just realized that I need to store the part number separately from
the ProcessPlan in tblOrders. Some parts are processed according to several
different process plans, and it is necessary to report statistics on the
part number regardless of the process plan. Selecting a process plan can
limit the choice of part numbers to just those associated with the process
plan, but the plan and the part do not always go together.

I believe I have figured out this part of the original question:
"...how does the person creating the record select a particular part number?
A ProcessPlan may be for several similar parts, but an Order is for just one
PartNumber. I see how the ProcessPlan can be selected, which could show
Operations and Parts, but how to select a particular part?
 

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

Top