B
BruceM
Not a very good subject line, but it's a bit hard to summarize. In
describing the database I will greatly simplify the situation.
Let's say that the company makes wooden bookcases that consist of shelves, a
top, and a bottom, all of which are in essence just boards; and sides, which
are boards, but they will be machined to include shelf pin holes, rabbets,
etc. The operations involved are:
Cut
Plane
Machine
Sand
Finish
These are stored in a Process table.
The ProcessPlan record for a shelf includes just the name of the component
and a part number. Related records list all of the operations: cut, plane,
sand, and finish. The ProcessPlan record for a side adds the machining
operation.
tblProcessPlan
PlanID (PK)
JobID (FK) - more on this in a moment
PartDescription
PartNumber
tblOperation
OpID (PK)
OpName (Cut, Plane, etc.)
tblPlanOp (junction table)
PlanOpID (PK)
PlanID (FK)
OpID (FK)
These are more or less static records after they have been created. The
active part of the database is based on the customer orders that come in.
The order is assigned a job number, and a ProcessPlan is selected. Since
the operations are related (one process: many operations), they get dragged
along in a subform. The quantity (100, in this example) is added. This is
in tblJob
tblJob
JobID (PK)
PlanID (FK)
Quantity
The idea behind the database is that when an operation needs to be repeated
the quantity is recorded. If 10 pieces are cut too large and need to be
re-cut, 90% of the parts passed through that operation on the first try.
Then at finish it is discovered that 10 parts out of 100 need to go back for
more sanding, which is again 90%. The cumulative percentage for that job is
90% * 90%, which is 81%. It doesn't matter if some of the parts that needed
to be re-cut also needed more sanding.
Here's the part I'm trying to sort out. I create a job record by way of a
form bound to tblJob by entering the job number, entering the quantity, and
selecting the ProcessPlan from a combo box. The ProcessPlan appears in a
subform, which itself has a continuous subform to list the processes. I
need to store the operations that needed extra work (cut and sand in the
example above). In order to make it as simple as possible for the operators
I would prefer that when they enter the information about reworked parts, I
envision that they will select the JobID (which will be on the work order).
This will show them a listing of the operations, and the quantity. The
operator will enter the number of parts, and Access will do the math. In
the example above, it will be recorded that 10 parts had to be re-cut.
Access will compare that to the 100 parts for the job, and will figure the
90%. Same for sanding, leaving the cumulative total at 91%.
The difficulty is that I only want to store information about operations
that needed extra work. I can imagine something like two continuous
subforms side-by-side: one for the "static" listing of the ProcessPlan, and
the other with "blanks" for entering quantities as needed. In the example
above, I only want to store the information about the cut and sand
operations; the other operations are assumed to be 100% OK. I can't work
out how to do this. I know it will be in a table (tblRework) related to
tblJob
This would be simplified if the operator picked the operation from a list,
then entered the quantity that needed rework. The subform record would be
created as needed, just as the operations were selected for the original
ProcessPlan from a list of all possible operations. In this case the list
of all possible operations would be limited to just the ones that were
originally selected for the ProcessPlan.
The best thing would be to have this all in front of the operator, with no
need to select the operation from a list, as if there was a listing of
operations on a piece of paper and a blank column for the rework quantity,
if needed. Also, some operations are performed twice. Let's say that
sanding is performed before and after machining. Sand1 and Sand2 are two
separate items on the process plan, but the operator ideally shouldn't be
choosing between the two. Rather, the operator will scan the list and see
Sand, Machine, and Sand in that order. If the problem occurred at the
second Sand operation, we need to know that the problem was there and not at
the previous one.
By the way, this is not my plan. I am only trying to implement it as
efficiently as is possible. The basic structure is not within my power to
alter. However, I will alter my approach to the structure if it makes sense
to do so.
Thanks for hanging in through the explanation. I made it as compact as I
could, but it still rattles on at some length.
describing the database I will greatly simplify the situation.
Let's say that the company makes wooden bookcases that consist of shelves, a
top, and a bottom, all of which are in essence just boards; and sides, which
are boards, but they will be machined to include shelf pin holes, rabbets,
etc. The operations involved are:
Cut
Plane
Machine
Sand
Finish
These are stored in a Process table.
The ProcessPlan record for a shelf includes just the name of the component
and a part number. Related records list all of the operations: cut, plane,
sand, and finish. The ProcessPlan record for a side adds the machining
operation.
tblProcessPlan
PlanID (PK)
JobID (FK) - more on this in a moment
PartDescription
PartNumber
tblOperation
OpID (PK)
OpName (Cut, Plane, etc.)
tblPlanOp (junction table)
PlanOpID (PK)
PlanID (FK)
OpID (FK)
These are more or less static records after they have been created. The
active part of the database is based on the customer orders that come in.
The order is assigned a job number, and a ProcessPlan is selected. Since
the operations are related (one process: many operations), they get dragged
along in a subform. The quantity (100, in this example) is added. This is
in tblJob
tblJob
JobID (PK)
PlanID (FK)
Quantity
The idea behind the database is that when an operation needs to be repeated
the quantity is recorded. If 10 pieces are cut too large and need to be
re-cut, 90% of the parts passed through that operation on the first try.
Then at finish it is discovered that 10 parts out of 100 need to go back for
more sanding, which is again 90%. The cumulative percentage for that job is
90% * 90%, which is 81%. It doesn't matter if some of the parts that needed
to be re-cut also needed more sanding.
Here's the part I'm trying to sort out. I create a job record by way of a
form bound to tblJob by entering the job number, entering the quantity, and
selecting the ProcessPlan from a combo box. The ProcessPlan appears in a
subform, which itself has a continuous subform to list the processes. I
need to store the operations that needed extra work (cut and sand in the
example above). In order to make it as simple as possible for the operators
I would prefer that when they enter the information about reworked parts, I
envision that they will select the JobID (which will be on the work order).
This will show them a listing of the operations, and the quantity. The
operator will enter the number of parts, and Access will do the math. In
the example above, it will be recorded that 10 parts had to be re-cut.
Access will compare that to the 100 parts for the job, and will figure the
90%. Same for sanding, leaving the cumulative total at 91%.
The difficulty is that I only want to store information about operations
that needed extra work. I can imagine something like two continuous
subforms side-by-side: one for the "static" listing of the ProcessPlan, and
the other with "blanks" for entering quantities as needed. In the example
above, I only want to store the information about the cut and sand
operations; the other operations are assumed to be 100% OK. I can't work
out how to do this. I know it will be in a table (tblRework) related to
tblJob
This would be simplified if the operator picked the operation from a list,
then entered the quantity that needed rework. The subform record would be
created as needed, just as the operations were selected for the original
ProcessPlan from a list of all possible operations. In this case the list
of all possible operations would be limited to just the ones that were
originally selected for the ProcessPlan.
The best thing would be to have this all in front of the operator, with no
need to select the operation from a list, as if there was a listing of
operations on a piece of paper and a blank column for the rework quantity,
if needed. Also, some operations are performed twice. Let's say that
sanding is performed before and after machining. Sand1 and Sand2 are two
separate items on the process plan, but the operator ideally shouldn't be
choosing between the two. Rather, the operator will scan the list and see
Sand, Machine, and Sand in that order. If the problem occurred at the
second Sand operation, we need to know that the problem was there and not at
the previous one.
By the way, this is not my plan. I am only trying to implement it as
efficiently as is possible. The basic structure is not within my power to
alter. However, I will alter my approach to the structure if it makes sense
to do so.
Thanks for hanging in through the explanation. I made it as compact as I
could, but it still rattles on at some length.