Third try

[BruceM]

I have asked a variant of this question twice in the tablesdbdesign group,
but maybe I went into too much detail, as I have not received a response
after several days.

In a machine shop the jobs are identified by a ProcessPlan number. The form
looks something like this (showing two separate records):

ProcessPlan: 06-01
PartNumber: 54321
Grind
SandBlast
Coat

ProcessPlan: 06-02
PartNumber: 12345
23456
Grind
Weld
Grind
SandBlast
Coat

This information is set up as a data entry chore before the jobs are
started. ProcessPlan and PartNumber are stored in the table tblPlan. Grind,
Weld, etc. are stored in a junction table (tjctPlanOperation). On the other
side of the junction table is tblOperations, in which Grind, Weld,
SandBlast, etc. are stored. It is a listing of all possible operations.

When the job arrives at the shop for processing, a record is created in the
table tblJob.

tblJob
JobID (PK)
PlanID (FK from tblPlan)
PartID (FK from tblPart)
JobDate
Quantity

PlanID is selected from a combo box. PartID is selected from a combo box,
the Row Source of which is determined by the selection in the PlanID combo
box.

Let's say that Job 0001 is to perform ProcessPlan 06-02 on part number
12345. The operator on the floor will have a work order with the job
number. In some cases he will need to report on one of the operations, in
which case he will select the JobNumber from a list of open jobs. I would
like him to see something like this:

Job: 0001
ProcessPlan: 06-02
PartNumber: 12345
Grind ____
Weld ____
Grind ____
SandBlast ____
Coat ____

Everything except the blanks after the Operations is in locked controls.
The idea is that the blanks can be populated with numbers as needed. The
main form record (the one bearing the job number) will be saved in all
cases, but the only saved subform records will be ones that contain numbers.

Just to be clear, the listing of operations was generated ahead of time, as
described above. It appears on the Operator's screen as a read-only diaplay
(because it is in a locked subform control). The only data entry for the
operator is the quantity if there is rework.

The trick is to make this as simple as is possible for the operator. I
think I could contrive a combo box Row Source that is a listing of all
operations for the selected ProcessPlan, and have the operator make a
selection, then enter a quantity. The information would be stored in a
child table. However, it would be best if the operator could click next to
the operation and enter the quantity. This would be especially helpful when
several operations (such as Grind) share the same name, since it would be
easier to select the correct one.

I could explain more as needed, but I will keep this as short as I can for
now so as not to scare people away. There is almost certainly some
ambiguity in what I have explained so far, but I won't try in this posting
to anticipate all questions.

 

[Jeff Boyce]

The folks who frequent these newsgroups are volunteers (at least, I'm not
aware of anyone being paid to be here <g>). If noone has responded to your
posts, it may be that none of the folks here have an idea to offer (or that
what you are asking is more complex that folks here can afford to contribute
their time on).

You may also want to consider seeking professional (i.e., paid)
assistance...

For what it's worth, try starting at the beginning ... and for a relational
database, the beginning is the underlying data. Try posting a description
of all of your tables (well, at least those involved in this issue), in the
same format as you posted your description of tblJob below.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Jeff,

I am a frequent contributor in these forums. I am well aware that people
who post here are volunteers. I mentioned that it was the third try in an
attempt to draw some attention to questions that have so far escaped notice
(or perhaps are too complex, but I do not know that).

The real-world situation (somewhat simplified): A company processes machine
parts. The various part numbers and descriptions are stored in tblParts.
The parts are processed according to Process Plans (stored in tblPlan). A
Process Plan record consists of the Plan Number (06-01 and a few other
details that do not matter for this question. A Process Plan also consists
of the Operations (which are stored in tblOp) involved in the Process, as
illustrated in the original posting in this thread (Grind, SandBlast, etc.).
These are stored in the junction table tjctPlanOp (more on this shortly). A
form/subform is used to set up the ProcessPlan record. The main form is
bound to tblProcessPlan, and the subform is bound to tjctPlanOp. A combo
box using tblOp as its record source is used to select the operations.

tblPlan
PlanID (PK)
PlanNumber

tblOp
OpID (PK)
OpDescription

tjctPlanOp
PlanOpID (PK)
PlanID_PlanOp (FK to tblPlan)
OpID_PlanOp (FK to tblOp)

It's a pretty usual sort of many-to-many.

The idea is that when a job is being processed, sometimes an operation goes
somewhat awry, and parts need to be processed through that operation again.
In this case the number of parts that needed rework needs to be recorded,
along with a way to identify the operation at which the rework occurred.
The record in tblJob is entered when the parts arrive in-house, before
processing has begun.

tblJob
JobID (PK)
PlanID_Job (FK to tblPlan)
JobNumber (from our in-house numbering system)
PartNumber
PartQuantity
JobDate

PartNumber, by the way, is in a many-to-many relationship similar to that
used for Operations, but I don't think that matters here.

Finally, there needs to be a (simple) way for operators on the floor to
enter the information, which for their purposes consists of selecting the
job, selecting the operation, and entering the rework quantity. To this end
I am using a form bound to tblJob, and a subform bound to tjctRework.

tjctRework
ReworkID (PK)
OpID_Rework (FK to tblOp)
JobID_Rework (FK to tblJob)
ReworkQuantity

This too is a junction table. One of the reasons for the junction tables is
reporting. It may be necessary to produce a report of the rework associated
with a particular operation such as SandBlasting for all jobs, etc.

Back to form for the operators to enter rework quantities. A main form is
bound to tblJob. The job information is read-only for the users on the shop
floor. The other thing that is displayed (also read-only) is a listing of
the operations involved in the ProcessPlan. Ideally the operator will
select an operation (let's say the second Grind operation), and enter the
rework quantity.

I have decided that a list box may be the best way to display the
operations. (The other option, I suppose, is a subform, using SQL somewhat
similar to the following, except that the Link Parent and Link Child
properties simplify the code; that is, the WHERE is not needed). The list
box is unbound. This is its Row Source SQL:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr, tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp ON tblJob.PlanID_Job =
tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

Its effect is to display a list of the operations associated with the
Process Plan that is being used for the job. Each row includes the name
(and PK) of the operation, the ID number associated with the ProcessPlan,
and the ID number associated with the process. In short, each row is
uniquely identified as being associated with a particular ProcessPlan and a
particular job. I would hope that by clicking on one of the rows I can by
some means enter a new record into tjctRework. Maybe there would be a
pop-up form in which the user could input the rework quantity. The SQL, by
the way, is the result of some experimentation. It produces the desired
result (a listing of operations associated with the ProcessPlan), but I
really am not sure it's the best way to get there.

Am I anywhere close to being on the right track? If so, is the pop-up form
a reasonable way to enter the rework quantity? If so, how can it be made to
pop up by clicking the appropriate row? Would that be a place to use
OpenArgs?

I can probably get this worked out eventually, but if I could get a little
push in the right direction (or get pulled back from the wrong course) it
would be a great help.

 

[Jeff Boyce]

Please see comments in-line below...

Jeff,

I am a frequent contributor in these forums. I am well aware that people
who post here are volunteers. I mentioned that it was the third try in an
attempt to draw some attention to questions that have so far escaped
notice (or perhaps are too complex, but I do not know that).

The real-world situation (somewhat simplified): A company processes
machine parts. The various part numbers and descriptions are stored in
tblParts.

No "layout" description of tblParts?

The parts are processed according to Process Plans (stored in tblPlan).

From what I found below, the "Process Plans" are actually stored in
tjctPlanOp, right? Isn't that the table that associates a given plan with
the operations that plan includes?

A Process Plan record consists of the Plan Number (06-01 and a few other
details that do not matter for this question. A Process Plan also
consists of the Operations (which are stored in tblOp) involved in the
Process, as illustrated in the original posting in this thread (Grind,
SandBlast, etc.). These are stored in the junction table tjctPlanOp (more
on this shortly). A form/subform is used to set up the ProcessPlan
record. The main form is bound to tblProcessPlan, and the subform is
bound to tjctPlanOp. A combo box using tblOp as its record source is used
to select the operations.

tblPlan
PlanID (PK)
PlanNumber

tblOp
OpID (PK)
OpDescription

tjctPlanOp
PlanOpID (PK)
PlanID_PlanOp (FK to tblPlan)
OpID_PlanOp (FK to tblOp)

It's a pretty usual sort of many-to-many.

The idea is that when a job

Is this the same as a Process Plan? Is this the set of related records in
tjctPlanOp?

is being processed, sometimes an operation goes somewhat awry, and parts
need to be processed through that operation again. In this case the number
of parts that needed rework needs to be recorded, along with a way to
identify the operation at which the rework occurred. The record in tblJob
is entered when the parts arrive in-house, before processing has begun.

It sounds like the "job" is the "processing" of a number of duplicate parts
(i.e., same part number).

tblJob
JobID (PK)
PlanID_Job (FK to tblPlan)
JobNumber (from our in-house numbering system)
PartNumber
PartQuantity
JobDate

PartNumber, by the way, is in a many-to-many relationship similar to that
used for Operations, but I don't think that matters here.

"many-to-many" to what?

Finally, there needs to be a (simple) way for operators on the floor to
enter the information, which for their purposes consists of selecting the
job, selecting the operation, and entering the rework quantity. To this
end I am using a form bound to tblJob, and a subform bound to tjctRework.

tjctRework
ReworkID (PK)
OpID_Rework (FK to tblOp)
JobID_Rework (FK to tblJob)
ReworkQuantity

Do you need a ReworkDate field in here too?

This too is a junction table. One of the reasons for the junction tables
is reporting. It may be necessary to produce a report of the rework
associated with a particular operation such as SandBlasting for all jobs,
etc.

If you want to report on rework associated with a particular operation
across all jobs, why are you recording the JobID in the Rework junction
table?

Back to form for the operators to enter rework quantities. A main form is
bound to tblJob. The job information is read-only for the users on the
shop floor. The other thing that is displayed (also read-only) is a
listing of the operations involved in the ProcessPlan. Ideally the
operator will select an operation (let's say the second Grind operation),
and enter the rework quantity.

"the second Grind operation" implies that there is some sequencing. I don't
recall seeing anything in your tjctPlanOp table that holds sequencing
info...

I have decided that a list box may be the best way to display the
operations. (The other option, I suppose, is a subform, using SQL
somewhat similar to the following, except that the Link Parent and Link
Child properties simplify the code; that is, the WHERE is not needed).
The list box is unbound. This is its Row Source SQL:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr, tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp ON tblJob.PlanID_Job =
tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

Its effect is to display a list of the operations associated with the
Process Plan that is being used for the job. Each row includes the name
(and PK) of the operation, the ID number associated with the ProcessPlan,
and the ID number associated with the process. In short, each row is
uniquely identified as being associated with a particular ProcessPlan and
a particular job.

If the PKs and IDs are being displayed too, this is probably more info than
the floor folks need to see.

I would hope that by clicking on one of the rows I can by some means enter
a new record into tjctRework. Maybe there would be a pop-up form in which
the user could input the rework quantity.

I can imagine a procedure, code behind a double-click event in the listbox,
that gathers the OpID (?and PlanID?), pops up an InputBox to gather the
Rework Quantity from the user, then writes the record.

The SQL, by the way, is the result of some experimentation. It produces
the desired result (a listing of operations associated with the
ProcessPlan), but I really am not sure it's the best way to get there.

Without having all the data you have, and spending time experimenting with
different approaches, I'm not sure that I could advise on a "best" way (not

Am I anywhere close to being on the right track? If so, is the pop-up
form a reasonable way to enter the rework quantity? If so, how can it be
made to pop up by clicking the appropriate row? Would that be a place to
use OpenArgs?

No need for OpenArgs. Take a look at InputBox.

I can probably get this worked out eventually, but if I could get a little
push in the right direction (or get pulled back from the wrong course) it
would be a great help.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Linson]

I've come on this rather late in the history of the thread, and it is late
(no, very early) which may have something to do with my puzzlement, but I
see that you've given a lot of information, but in an order that I am having
difficulty following what you are describing.

Try reviewing what you want to describe from the POV that the readers do NOT
know what it is you are doing, don't know the real-life process your
database is modeling, and need to be led very logically through the
requirements... I'd guess it's not logical to start from the form you want
to use before describing anything else.

Certainly with three threads for the same question, and several fruitless
attempts in this last one, it would appear that I'm not the only one who's
confused. Consider carefully what you might do to UNconfuse us.

Larry Linson
Microsoft Access MVP

Jeff,

I am a frequent contributor in these forums. I am well aware that people
who post here are volunteers. I mentioned that it was the third try in an
attempt to draw some attention to questions that have so far escaped
notice (or perhaps are too complex, but I do not know that).

The real-world situation (somewhat simplified): A company processes
machine parts. The various part numbers and descriptions are stored in
tblParts. The parts are processed according to Process Plans (stored in
tblPlan). A Process Plan record consists of the Plan Number (06-01 and a
few other details that do not matter for this question. A Process Plan
also consists of the Operations (which are stored in tblOp) involved in
the Process, as illustrated in the original posting in this thread (Grind,
SandBlast, etc.). These are stored in the junction table tjctPlanOp (more
on this shortly). A form/subform is used to set up the ProcessPlan
record. The main form is bound to tblProcessPlan, and the subform is
bound to tjctPlanOp. A combo box using tblOp as its record source is used
to select the operations.

tblPlan
PlanID (PK)
PlanNumber

tblOp
OpID (PK)
OpDescription

tjctPlanOp
PlanOpID (PK)
PlanID_PlanOp (FK to tblPlan)
OpID_PlanOp (FK to tblOp)

It's a pretty usual sort of many-to-many.

The idea is that when a job is being processed, sometimes an operation
goes somewhat awry, and parts need to be processed through that operation
again. In this case the number of parts that needed rework needs to be
recorded, along with a way to identify the operation at which the rework
occurred. The record in tblJob is entered when the parts arrive in-house,
before processing has begun.

tblJob
JobID (PK)
PlanID_Job (FK to tblPlan)
JobNumber (from our in-house numbering system)
PartNumber
PartQuantity
JobDate

PartNumber, by the way, is in a many-to-many relationship similar to that
used for Operations, but I don't think that matters here.

Finally, there needs to be a (simple) way for operators on the floor to
enter the information, which for their purposes consists of selecting the
job, selecting the operation, and entering the rework quantity. To this
end I am using a form bound to tblJob, and a subform bound to tjctRework.

tjctRework
ReworkID (PK)
OpID_Rework (FK to tblOp)
JobID_Rework (FK to tblJob)
ReworkQuantity

This too is a junction table. One of the reasons for the junction tables
is reporting. It may be necessary to produce a report of the rework
associated with a particular operation such as SandBlasting for all jobs,
etc.

Back to form for the operators to enter rework quantities. A main form is
bound to tblJob. The job information is read-only for the users on the
shop floor. The other thing that is displayed (also read-only) is a
listing of the operations involved in the ProcessPlan. Ideally the
operator will select an operation (let's say the second Grind operation),
and enter the rework quantity.

I have decided that a list box may be the best way to display the
operations. (The other option, I suppose, is a subform, using SQL
somewhat similar to the following, except that the Link Parent and Link
Child properties simplify the code; that is, the WHERE is not needed).
The list box is unbound. This is its Row Source SQL:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr, tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp ON tblJob.PlanID_Job =
tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

Its effect is to display a list of the operations associated with the
Process Plan that is being used for the job. Each row includes the name
(and PK) of the operation, the ID number associated with the ProcessPlan,
and the ID number associated with the process. In short, each row is
uniquely identified as being associated with a particular ProcessPlan and
a particular job. I would hope that by clicking on one of the rows I can
by some means enter a new record into tjctRework. Maybe there would be a
pop-up form in which the user could input the rework quantity. The SQL,
by the way, is the result of some experimentation. It produces the
desired result (a listing of operations associated with the ProcessPlan),
but I really am not sure it's the best way to get there.

Am I anywhere close to being on the right track? If so, is the pop-up
form a reasonable way to enter the rework quantity? If so, how can it be
made to pop up by clicking the appropriate row? Would that be a place to
use OpenArgs?

I can probably get this worked out eventually, but if I could get a little
push in the right direction (or get pulled back from the wrong course) it
would be a great help.

The folks who frequent these newsgroups are volunteers (at least, I'm not
aware of anyone being paid to be here <g>). If noone has responded to
your posts, it may be that none of the folks here have an idea to offer
(or that what you are asking is more complex that folks here can afford
to contribute their time on).

You may also want to consider seeking professional (i.e., paid)
assistance...

For what it's worth, try starting at the beginning ... and for a
relational database, the beginning is the underlying data. Try posting a
description of all of your tables (well, at least those involved in this
issue), in the same format as you posted your description of tblJob
below.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Thanks very much for your responses. Comments in-line below. I wrote a lot
of stuff, but I really have only one question (unless somebody feels
inclined to make suggestions). I have marked the question with a line of
asterisks. The database is fairly difficult to describe, but seems to be
working as intended, except that I have the question. Also, I have left out
a few details that do not seem important in this context.

Please see comments in-line below...



No "layout" description of tblParts?

If you mean how it fits into the structure, it goes like this:
A ProcessPlan is a predetermined set of operations for processing a part or
a category of partsthat is developed by Engineering and approved by the
customer; a recipe, if you will. The cookbook is written before the meal
preparation begins.
A Process Plan has a unique identifying number such as 06-01. This is
stored in tblPlan. Since the numbering system could conceivably change, a
separate autonumber field is the PK. Autonumber is the PK for all tables.
All FK fields are therefore Long Integer.
A Process Plan consists of Operations such as Grind, Blast, etc. There is a
finite list of operations used for all Process Plans. These Operations are
stored in tblOp.
A Process Plan may be for one part, or for several parts that are very
similar. The Part Numbers are customer designations. They are stored in
tblPart.
A Process Plan consists of many operations, and each operation may be part
of many Process Plans (many-to-many). The junction table tjctPlanOp
resolves this relationship.
A Process Plan may be for many parts, and a part may be processed according
to several different ProcessPlans (many-to-many). The junction table
tjctPlanPart resolves this relationship.

tblProcessPlan
PlanID (PK)
PlanNumber (our designation)

tblOp
OpID (PK)
OpDescr (Grind, Blast, etc.)

tjctPlanOp
PlanOpID (PK)
PlanID_PlanOp (FK to tblProcessPlan)
OpID_PlanOp (FK to tblOp)

tblPart
PartID (PK)
PartNumber (customer designation)
PartDescription

tjctPlanPart
PlanPartID (PK)
PlanID_PlanPart (FK to tblProcessPlan)
PartID_PlanPart (FK to tblPart)

This information is entered as a data entry chore before any work is done.
There is a form (frmProcessPlan) based on tblProcessPlan, with subforms
based on tjctPlanOp (fsubPlanOp) and tjctPlanPart (fsubPlanPart).
fsubPlanOp has a combo box bound to PlanID_PlanOp (the FK field in
tjctPlanOp). The row source for the combo box is tblOp. In similar manner,
fsubPlanPart has a combo box bound to PlanID_PlanPart (the FK field in
tjctPlanPart). The row source for the combo box is tblPart. The user
enters the Plan identifying number in the main form, selects operations to
populate the fsubPlanOp record source (tjctPlanOp), and selects parts to
populate the fsubPlanPart record source (tjctPlanPart).
This results in a ProcessPlan with a list of associated Operations, and a
list of Parts that may be processed according to the ProcessPlan. This
information sits in the database until needed.

From what I found below, the "Process Plans" are actually stored in
tjctPlanOp, right? Isn't that the table that associates a given plan with
the operations that plan includes?

I hope I have succeeded in explaining above that the ProcessPlan is stored
in its own table, but contains very few details. I will just mention that
there are some description fields and such that do not enter into the
discussion, so I have left them out of the description. tjctPlanOp stores a
list of Operations that are associated with a particular ProcessPlan.

Is this the same as a Process Plan? Is this the set of related records in
tjctPlanOp?

No. A job is a specific order sent in by a customer to process a single
part number according to a specified ProcessPlan. Back to the earlier
analogy, the ProcessPlans are recipes that are written down, and are stored
until needed. In a restaurant the waiter takes the Order (one item per
customer in this strange restaurant, but they can order any quantity of that
one item), then brings it into the kitchen where it is processed according
to the recipe specified on the Order. The recipe consists of one item from
the menu (the PartNumber), which is processed through several operations
(Chop, Fry, etc.) according to the recipe (ProcessPlan).
The recipe is the ProcessPlan. The menu is the list of Parts. The Order is
a menu item (Part) that is processed according to a recipe (ProcessPlan).

An Order is a Job. Jobs are stored in tblJob.

tblJob
JobID (PK)
PartID_Job (FK to tblPart)
PlanID_Job (FK to tblProcessPlan)
JobDate
JobNumber (from our in-house numbering system)
Quantity (number of parts being processed)
Completed (Y/N)

A form (frmJob) bound to tblJob is used for entering this information. The
Receiving department will enter the JobNumber and the Quantity of parts
being processed. They will select the ProcessPlan specified by the
customer, and will be presented with a filtered list of Parts that may be
processed according to the specified ProcessPlan. They will select one
part. Selection is done by way of combo boxes. These selections will be
bound to the two FK fields in tblJob.

Let's say the job is for 100 parts (that is, 100 separate instances of a
single part number). If the job is processed without a hitch there is no
more data entry except to mark the job as complete. However, if it is
discovered at, say, the Blast operation that the previous Grind operation is
incomplete for 10 out of 100 parts, the parts are sent back to the Grind
operator for rework, and the quanity sent back is recorded. One more table
comes into play to record this information. Each Job may contain several
Operations (from the ProcessPlan that is associated with the job) needing
rework, and each Operation (Grind, for instance) may be associated with any
number of different Jobs, so there is a junction table (tjctRework) between
tblJob and tblOp. I'm not sure this relationship is necessary or helpful,
but I think it is since the aim is to select an Operation and generate a
report on how often it needs rework. The idea is that an operation needing
frequent rework may need to be redesigned. This database's purpose is to
identify such things.

tjctRework
ReworkID (PK)
JobID_Rework (FK to tblJob)
OpID_Rework (FK to tblOp)
QuantityReworked

Here is the part where the operator enters this information. The operator
on the shop floor will select the JobNumber (which is on the paper Order he
has in hand), and will be presented with a form bound to tblJob. He will
see only the necessary information: JobNumber, PlanNumber, PartNumber and
Quantity are probably enough. Therre will also be a listing of the
Operations associated with the Process Plan. The following SQL (copied from
the previous posting) can be used to populate an unbound list box:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr,
tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp
ON tblJob.PlanID_Job = tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

I don't know if it is the most efficient SQL, but it produces a listing of
Operations in the order they are performed for the particular ProcessPlan.
I will mention that for now the autonumber PK in tjctPlanOp determines the
order in which the operations are listed, but I will change that to an
incrementing number (using DMax and adding 1 for each new record).

During the ProcessPlan data entry chore described above, the user enters
something like:

ProcessPlan: 06-01
Parts:
123456
654321
Operations:
Grind
Blast
Weld
Grind
Burnish

At Receiving they assign a JobNumber, enter the number of parts, select a
ProcessPlan (06-01, for instance), and select one of the parts that may be
processed according to that ProcessPlan (123456, for instance).

The Operators on the floor see something like:

JobID: 00022 (PK is 999 - not seen by users)
ProcessPlan: 06-01 (key field is 02 - not seen by users))
Part Number: 123456
Quantity: 100
Operations:
Grind (01)
Blast (02)
Weld (03)
Grind (04)
Blast (05)
Burnish (06)

The numbers in parentheses for the Operations represent the PK from
tblPlanOp. The operators do not see them. The Operations are listed in the
list box. The point here is that each of the listed Operations is
associated with a ProcessPlan and a Job.

Let's say that at the second Blast operation it is discovered that more
Grinding is needed on 10 of the parts. They will select the JobNumber from
among open jobs, and will see the above (minus the stuff in parentheses). I
would like them to click on the second Grind operation (the one with the
number 04, but all they need to see is that it is the one after the Weld
operation). This will open a pop-up form in which they will enter the
number of parts reworked. The record thus entered needs to be associated
with the Job, the ProcessPlan being used for the Job, and the particular
Operation (04 in this case). There may be a Comments field, and maybe one
or two other items, which is why I am not inclined to use an Input box,
which I do not believe will work well for entering data into multiple fields
(but maybe I'm wrong).

It sounds like the "job" is the "processing" of a number of duplicate
parts (i.e., same part number).
Exactly.

"many-to-many" to what?

I hope I have explained this adequately above.

Do you need a ReworkDate field in here too?

Probably not. The job has a start and end date (I didn't mention the end
date).

If you want to report on rework associated with a particular operation
across all jobs, why are you recording the JobID in the Rework junction
table?

We may need to go back to a particular job and view its history.

"the second Grind operation" implies that there is some sequencing. I
don't recall seeing anything in your tjctPlanOp table that holds
sequencing info...

As I mentioned, I intend to use a number incremented with code, so that the
first Operation entered for a ProcessPlan has a lower number than the next
Operation entered.

I have decided that a list box may be the best way to display the
operations. (The other option, I suppose, is a subform, using SQL
somewhat similar to the following, except that the Link Parent and Link
Child properties simplify the code; that is, the WHERE is not needed).
The list box is unbound. This is its Row Source SQL:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr, tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp ON tblJob.PlanID_Job =
tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

Its effect is to display a list of the operations associated with the
Process Plan that is being used for the job. Each row includes the name
(and PK) of the operation, the ID number associated with the ProcessPlan,
and the ID number associated with the process. In short, each row is
uniquely identified as being associated with a particular ProcessPlan and
a particular job.

If the PKs and IDs are being displayed too, this is probably more info
than the floor folks need to see.

Agreed. I don't intend them to see any of the key fields.

I can imagine a procedure, code behind a double-click event in the
listbox, that gathers the OpID (?and PlanID?), pops up an InputBox to
gather the Rework Quantity from the user, then writes the record.


Without having all the data you have, and spending time experimenting with
different approaches, I'm not sure that I could advise on a "best" way
(not that I'm volunteering!<g>)

***********************
My *only* question at this point has to do with entering the rework
information. A rework record needs to be associated with a Job, a Part
(which is included with the Job record), a ProcessPlan (also included in the
Job record), the specific Operation from the ProcessPlan (04 in the example
above), and the generic name of the Operation (such as Grind, which the
database identifies by its PK in tblOp). This information is contained
either in the list box or in the Job record.
It would be literally possible to display all of the key fields, and have
the operator copy them to the Rework record. However, the only reasonable
way to do this is to have the key fields automatically included in the
Rework record.

No need for OpenArgs. Take a look at InputBox.

Discussed briefly above. Will it work for multiple fields? I need the
database to be as flexible as is possible, in case features need to be added
later.

 

[BruceM]

Thanks for taking the time to look at this. I have taken another stab at
describing this in my second reply to Jeff. I had thought that I was
describing the table structure first, but apparently I was unclear. I
really think I clarified the situation in my reply.
My frustration when I started this thread came from the fact that there was
no reply at all to the first posts, yet I have seen fairly complex
situations discussed at length. I didn't know if my posts were unclear, too
complex, or what. In my attempt to simplify and shorten the questions I
ended up omitting important information.

I've come on this rather late in the history of the thread, and it is late
(no, very early) which may have something to do with my puzzlement, but I
see that you've given a lot of information, but in an order that I am
having difficulty following what you are describing.

Try reviewing what you want to describe from the POV that the readers do
NOT know what it is you are doing, don't know the real-life process your
database is modeling, and need to be led very logically through the
requirements... I'd guess it's not logical to start from the form you want
to use before describing anything else.

Certainly with three threads for the same question, and several fruitless
attempts in this last one, it would appear that I'm not the only one who's
confused. Consider carefully what you might do to UNconfuse us.

Larry Linson
Microsoft Access MVP

Jeff,

I am a frequent contributor in these forums. I am well aware that people
who post here are volunteers. I mentioned that it was the third try in
an attempt to draw some attention to questions that have so far escaped
notice (or perhaps are too complex, but I do not know that).

The real-world situation (somewhat simplified): A company processes
machine parts. The various part numbers and descriptions are stored in
tblParts. The parts are processed according to Process Plans (stored in
tblPlan). A Process Plan record consists of the Plan Number (06-01 and a
few other details that do not matter for this question. A Process Plan
also consists of the Operations (which are stored in tblOp) involved in
the Process, as illustrated in the original posting in this thread
(Grind, SandBlast, etc.). These are stored in the junction table
tjctPlanOp (more on this shortly). A form/subform is used to set up the
ProcessPlan record. The main form is bound to tblProcessPlan, and the
subform is bound to tjctPlanOp. A combo box using tblOp as its record
source is used to select the operations.

tblPlan
PlanID (PK)
PlanNumber

tblOp
OpID (PK)
OpDescription

tjctPlanOp
PlanOpID (PK)
PlanID_PlanOp (FK to tblPlan)
OpID_PlanOp (FK to tblOp)

It's a pretty usual sort of many-to-many.

The idea is that when a job is being processed, sometimes an operation
goes somewhat awry, and parts need to be processed through that operation
again. In this case the number of parts that needed rework needs to be
recorded, along with a way to identify the operation at which the rework
occurred. The record in tblJob is entered when the parts arrive in-house,
before processing has begun.

tblJob
JobID (PK)
PlanID_Job (FK to tblPlan)
JobNumber (from our in-house numbering system)
PartNumber
PartQuantity
JobDate

PartNumber, by the way, is in a many-to-many relationship similar to that
used for Operations, but I don't think that matters here.

Finally, there needs to be a (simple) way for operators on the floor to
enter the information, which for their purposes consists of selecting the
job, selecting the operation, and entering the rework quantity. To this
end I am using a form bound to tblJob, and a subform bound to tjctRework.

tjctRework
ReworkID (PK)
OpID_Rework (FK to tblOp)
JobID_Rework (FK to tblJob)
ReworkQuantity

This too is a junction table. One of the reasons for the junction tables
is reporting. It may be necessary to produce a report of the rework
associated with a particular operation such as SandBlasting for all jobs,
etc.

Back to form for the operators to enter rework quantities. A main form
is bound to tblJob. The job information is read-only for the users on
the shop floor. The other thing that is displayed (also read-only) is a
listing of the operations involved in the ProcessPlan. Ideally the
operator will select an operation (let's say the second Grind operation),
and enter the rework quantity.

I have decided that a list box may be the best way to display the
operations. (The other option, I suppose, is a subform, using SQL
somewhat similar to the following, except that the Link Parent and Link
Child properties simplify the code; that is, the WHERE is not needed).
The list box is unbound. This is its Row Source SQL:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr, tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp ON tblJob.PlanID_Job =
tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

Its effect is to display a list of the operations associated with the
Process Plan that is being used for the job. Each row includes the name
(and PK) of the operation, the ID number associated with the ProcessPlan,
and the ID number associated with the process. In short, each row is
uniquely identified as being associated with a particular ProcessPlan and
a particular job. I would hope that by clicking on one of the rows I can
by some means enter a new record into tjctRework. Maybe there would be a
pop-up form in which the user could input the rework quantity. The SQL,
by the way, is the result of some experimentation. It produces the
desired result (a listing of operations associated with the ProcessPlan),
but I really am not sure it's the best way to get there.

Am I anywhere close to being on the right track? If so, is the pop-up
form a reasonable way to enter the rework quantity? If so, how can it be
made to pop up by clicking the appropriate row? Would that be a place to
use OpenArgs?

I can probably get this worked out eventually, but if I could get a
little push in the right direction (or get pulled back from the wrong
course) it would be a great help.

The folks who frequent these newsgroups are volunteers (at least, I'm
not aware of anyone being paid to be here <g>). If noone has responded
to your posts, it may be that none of the folks here have an idea to
offer (or that what you are asking is more complex that folks here can
afford to contribute their time on).

You may also want to consider seeking professional (i.e., paid)
assistance...

For what it's worth, try starting at the beginning ... and for a
relational database, the beginning is the underlying data. Try posting
a description of all of your tables (well, at least those involved in
this issue), in the same format as you posted your description of tblJob
below.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have asked a variant of this question twice in the tablesdbdesign
group, but maybe I went into too much detail, as I have not received a
response after several days.

In a machine shop the jobs are identified by a ProcessPlan number. The
form looks something like this (showing two separate records):

ProcessPlan: 06-01
PartNumber: 54321
Grind
SandBlast
Coat

ProcessPlan: 06-02
PartNumber: 12345
23456
Grind
Weld
Grind
SandBlast
Coat

This information is set up as a data entry chore before the jobs are
started. ProcessPlan and PartNumber are stored in the table tblPlan.
Grind, Weld, etc. are stored in a junction table (tjctPlanOperation).
On the other side of the junction table is tblOperations, in which
Grind, Weld, SandBlast, etc. are stored. It is a listing of all
possible operations.

When the job arrives at the shop for processing, a record is created in
the table tblJob.

tblJob
JobID (PK)
PlanID (FK from tblPlan)
PartID (FK from tblPart)
JobDate
Quantity

PlanID is selected from a combo box. PartID is selected from a combo
box, the Row Source of which is determined by the selection in the
PlanID combo box.

Let's say that Job 0001 is to perform ProcessPlan 06-02 on part number
12345. The operator on the floor will have a work order with the job
number. In some cases he will need to report on one of the operations,
in which case he will select the JobNumber from a list of open jobs. I
would like him to see something like this:

Job: 0001
ProcessPlan: 06-02
PartNumber: 12345
Grind ____
Weld ____
Grind ____
SandBlast ____
Coat ____

Everything except the blanks after the Operations is in locked
controls. The idea is that the blanks can be populated with numbers as
needed. The main form record (the one bearing the job number) will be
saved in all cases, but the only saved subform records will be ones
that contain numbers.

Just to be clear, the listing of operations was generated ahead of
time, as described above. It appears on the Operator's screen as a
read-only diaplay (because it is in a locked subform control). The
only data entry for the operator is the quantity if there is rework.

The trick is to make this as simple as is possible for the operator. I
think I could contrive a combo box Row Source that is a listing of all
operations for the selected ProcessPlan, and have the operator make a
selection, then enter a quantity. The information would be stored in a
child table. However, it would be best if the operator could click
next to the operation and enter the quantity. This would be especially
helpful when several operations (such as Grind) share the same name,
since it would be easier to select the correct one.

I could explain more as needed, but I will keep this as short as I can
for now so as not to scare people away. There is almost certainly some
ambiguity in what I have explained so far, but I won't try in this
posting to anticipate all questions.

 

[Jeff Boyce]

in-line comments on in-line comments on in-line comments<g>!

Thanks very much for your responses. Comments in-line below. I wrote a lot
of stuff, but I really have only one question (unless somebody feels
inclined to make suggestions). I have marked the question with a line of
asterisks. The database is fairly difficult to describe, but seems to be
working as intended, except that I have the question. Also, I have left out
a few details that do not seem important in this context.



If you mean how it fits into the structure, it goes like this:

No, I didn't. I just meant the table structure, like the other tables you
showed. The additional information below is too much detail.

A ProcessPlan is a predetermined set of operations for processing a part or
a category of partsthat is developed by Engineering and approved by the
customer; a recipe, if you will. The cookbook is written before the meal
preparation begins.
A Process Plan has a unique identifying number such as 06-01. This is
stored in tblPlan. Since the numbering system could conceivably change, a
separate autonumber field is the PK. Autonumber is the PK for all tables.
All FK fields are therefore Long Integer.
A Process Plan consists of Operations such as Grind, Blast, etc. There is a
finite list of operations used for all Process Plans. These Operations are
stored in tblOp.
A Process Plan may be for one part, or for several parts that are very
similar. The Part Numbers are customer designations. They are stored in
tblPart.
A Process Plan consists of many operations, and each operation may be part
of many Process Plans (many-to-many). The junction table tjctPlanOp
resolves this relationship.
A Process Plan may be for many parts, and a part may be processed according
to several different ProcessPlans (many-to-many). The junction table
tjctPlanPart resolves this relationship.

tblProcessPlan
PlanID (PK)
PlanNumber (our designation)

tblOp
OpID (PK)
OpDescr (Grind, Blast, etc.)

tjctPlanOp
PlanOpID (PK)
PlanID_PlanOp (FK to tblProcessPlan)
OpID_PlanOp (FK to tblOp)

tblPart
PartID (PK)
PartNumber (customer designation)
PartDescription

tjctPlanPart
PlanPartID (PK)
PlanID_PlanPart (FK to tblProcessPlan)
PartID_PlanPart (FK to tblPart)

This information is entered as a data entry chore before any work is done.
There is a form (frmProcessPlan) based on tblProcessPlan, with subforms
based on tjctPlanOp (fsubPlanOp) and tjctPlanPart (fsubPlanPart).
fsubPlanOp has a combo box bound to PlanID_PlanOp (the FK field in
tjctPlanOp). The row source for the combo box is tblOp. In similar manner,
fsubPlanPart has a combo box bound to PlanID_PlanPart (the FK field in
tjctPlanPart). The row source for the combo box is tblPart. The user
enters the Plan identifying number in the main form, selects operations to
populate the fsubPlanOp record source (tjctPlanOp), and selects parts to
populate the fsubPlanPart record source (tjctPlanPart).
This results in a ProcessPlan with a list of associated Operations, and a
list of Parts that may be processed according to the ProcessPlan. This
information sits in the database until needed.

I hope I have succeeded in explaining above that the ProcessPlan is stored
in its own table, but contains very few details. I will just mention that
there are some description fields and such that do not enter into the
discussion, so I have left them out of the description. tjctPlanOp stores a
list of Operations that are associated with a particular ProcessPlan.

Too much detail... too easy to get lost in details and not see the basic
bones underneath.

No. A job is a specific order sent in by a customer to process a single
part number according to a specified ProcessPlan. Back to the earlier
analogy, the ProcessPlans are recipes that are written down, and are stored
until needed. In a restaurant the waiter takes the Order (one item per
customer in this strange restaurant, but they can order any quantity of that
one item), then brings it into the kitchen where it is processed according
to the recipe specified on the Order. The recipe consists of one item from
the menu (the PartNumber), which is processed through several operations
(Chop, Fry, etc.) according to the recipe (ProcessPlan).
The recipe is the ProcessPlan. The menu is the list of Parts. The Order is
a menu item (Part) that is processed according to a recipe (ProcessPlan).

An Order is a Job. Jobs are stored in tblJob.

tblJob
JobID (PK)
PartID_Job (FK to tblPart)
PlanID_Job (FK to tblProcessPlan)
JobDate
JobNumber (from our in-house numbering system)
Quantity (number of parts being processed)
Completed (Y/N)

A form (frmJob) bound to tblJob is used for entering this information. The
Receiving department will enter the JobNumber and the Quantity of parts
being processed. They will select the ProcessPlan specified by the
customer, and will be presented with a filtered list of Parts that may be
processed according to the specified ProcessPlan. They will select one
part. Selection is done by way of combo boxes. These selections will be
bound to the two FK fields in tblJob.

Let's say the job is for 100 parts (that is, 100 separate instances of a
single part number). If the job is processed without a hitch there is no
more data entry except to mark the job as complete. However, if it is
discovered at, say, the Blast operation that the previous Grind operation is
incomplete for 10 out of 100 parts, the parts are sent back to the Grind
operator for rework, and the quanity sent back is recorded. One more table
comes into play to record this information. Each Job may contain several
Operations (from the ProcessPlan that is associated with the job) needing
rework, and each Operation (Grind, for instance) may be associated with any
number of different Jobs, so there is a junction table (tjctRework) between
tblJob and tblOp. I'm not sure this relationship is necessary or helpful,
but I think it is since the aim is to select an Operation and generate a
report on how often it needs rework. The idea is that an operation needing
frequent rework may need to be redesigned. This database's purpose is to
identify such things.

tjctRework
ReworkID (PK)
JobID_Rework (FK to tblJob)
OpID_Rework (FK to tblOp)
QuantityReworked

Here is the part where the operator enters this information. The operator
on the shop floor will select the JobNumber (which is on the paper Order he
has in hand), and will be presented with a form bound to tblJob. He will
see only the necessary information: JobNumber, PlanNumber, PartNumber and
Quantity are probably enough. Therre will also be a listing of the
Operations associated with the Process Plan. The following SQL (copied from
the previous posting) can be used to populate an unbound list box:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr,
tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp
ON tblJob.PlanID_Job = tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

I don't know if it is the most efficient SQL, but it produces a listing of
Operations in the order they are performed for the particular ProcessPlan.
I will mention that for now the autonumber PK in tjctPlanOp determines the
order in which the operations are listed, but I will change that to an
incrementing number (using DMax and adding 1 for each new record).

During the ProcessPlan data entry chore described above, the user enters
something like:

ProcessPlan: 06-01
Parts:
123456
654321
Operations:
Grind
Blast
Weld
Grind
Burnish

At Receiving they assign a JobNumber, enter the number of parts, select a
ProcessPlan (06-01, for instance), and select one of the parts that may be
processed according to that ProcessPlan (123456, for instance).

The Operators on the floor see something like:

JobID: 00022 (PK is 999 - not seen by users)
ProcessPlan: 06-01 (key field is 02 - not seen by users))
Part Number: 123456
Quantity: 100
Operations:
Grind (01)
Blast (02)
Weld (03)
Grind (04)
Blast (05)
Burnish (06)

The numbers in parentheses for the Operations represent the PK from
tblPlanOp. The operators do not see them. The Operations are listed in the
list box. The point here is that each of the listed Operations is
associated with a ProcessPlan and a Job.

Let's say that at the second Blast operation it is discovered that more
Grinding is needed on 10 of the parts. They will select the JobNumber from
among open jobs, and will see the above (minus the stuff in parentheses). I
would like them to click on the second Grind operation (the one with the
number 04, but all they need to see is that it is the one after the Weld
operation). This will open a pop-up form in which they will enter the
number of parts reworked. The record thus entered needs to be associated
with the Job, the ProcessPlan being used for the Job, and the particular
Operation (04 in this case). There may be a Comments field, and maybe one
or two other items, which is why I am not inclined to use an Input box,
which I do not believe will work well for entering data into multiple fields
(but maybe I'm wrong).

It sounds like the "job" is the "processing" of a number of duplicate
parts (i.e., same part number).
Exactly.

"many-to-many" to what?

I hope I have explained this adequately above.

Do you need a ReworkDate field in here too?

Probably not. The job has a start and end date (I didn't mention the end
date).

If you want to report on rework associated with a particular operation
across all jobs, why are you recording the JobID in the Rework junction
table?

We may need to go back to a particular job and view its history.

"the second Grind operation" implies that there is some sequencing. I
don't recall seeing anything in your tjctPlanOp table that holds
sequencing info...

As I mentioned, I intend to use a number incremented with code, so that the
first Operation entered for a ProcessPlan has a lower number than the next
Operation entered.

I lost track of that "tree" for the forest ...

I have decided that a list box may be the best way to display the
operations. (The other option, I suppose, is a subform, using SQL
somewhat similar to the following, except that the Link Parent and Link
Child properties simplify the code; that is, the WHERE is not needed).
The list box is unbound. This is its Row Source SQL:

SELECT tjctPlanOp.PlanOpID, tblOp.OpDescr, tjctPlanOp.PlanID_PlanOp,
tjctPlanOp.OpID_PlanOp, tblJob.JobNum, tblJob.JobID
FROM tblOp
INNER JOIN (tblJob RIGHT JOIN tjctPlanOp ON tblJob.PlanID_Job =
tjctPlanOp.PlanID_PlanOp)
ON tblOp.OpID = tjctPlanOp.OpID_PlanOp
WHERE (((tblJob.JobID)=[txtJobID]))
ORDER BY tjctPlanOp.PlanOpID;

Its effect is to display a list of the operations associated with the
Process Plan that is being used for the job. Each row includes the name
(and PK) of the operation, the ID number associated with the ProcessPlan,
and the ID number associated with the process. In short, each row is
uniquely identified as being associated with a particular ProcessPlan and
a particular job.

If the PKs and IDs are being displayed too, this is probably more info
than the floor folks need to see.

Agreed. I don't intend them to see any of the key fields.

I can imagine a procedure, code behind a double-click event in the
listbox, that gathers the OpID (?and PlanID?), pops up an InputBox to
gather the Rework Quantity from the user, then writes the record.


Without having all the data you have, and spending time experimenting with
different approaches, I'm not sure that I could advise on a "best" way
(not that I'm volunteering!<g>)

***********************
My *only* question at this point has to do with entering the rework
information. A rework record needs to be associated with a Job, a Part
(which is included with the Job record), a ProcessPlan (also included in the
Job record), the specific Operation from the ProcessPlan (04 in the example
above), and the generic name of the Operation (such as Grind, which the
database identifies by its PK in tblOp). This information is contained
either in the list box or in the Job record.

You and I may have differing definitions of the word "record". Since Access
is a relational database, and it appears you have spent some time
normalizing your data structure, you absolutely do NOT need to store all
those details. You only need to store a PlanOpID (which points back to the
Plan and the Operation) and a JobID (which points back to a part, and
date/time info). All the other facts in your "record" above can be gathered
via queries, so redundantly storing them in a "rework record" is
unnecessary.

It would be literally possible to display all of the key fields, and have
the operator copy them to the Rework record. However, the only reasonable
way to do this is to have the key fields automatically included in the
Rework record.

See previous comment. See my suggestion for using an Input Box to gather
the only piece of info you need from the shop floor ... the #parts. All the
rest of the details would be available for picking (job, operation, ...).

Discussed briefly above. Will it work for multiple fields? I need the
database to be as flexible as is possible, in case features need to be added
later.

Again, you don't need multiple fields, only the one fact (#parts).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[BruceM]

Thanks again. I have pared down this reply to a couple of specific points.
Inline again, but much briefer (really).

You and I may have differing definitions of the word "record". Since
Access
is a relational database, and it appears you have spent some time
normalizing your data structure, you absolutely do NOT need to store all
those details. You only need to store a PlanOpID (which points back to
the
Plan and the Operation) and a JobID (which points back to a part, and
date/time info). All the other facts in your "record" above can be
gathered
via queries, so redundantly storing them in a "rework record" is
unnecessary.

Yes, I see now that PlanID is redundant, as PlanOpID links to that
information. The redundancy is my mistake, not a different definition of
"Record".

See previous comment. See my suggestion for using an Input Box to gather
the only piece of info you need from the shop floor ... the #parts. All
the
rest of the details would be available for picking (job, operation, ...).

Something that got lost in my previous post is that it is very likely there
will need to be a comments field or some such thing. Most likely the rework
record will need data entered into a field or fields other than
ReworkQuantity. If it is possible to use an input box for multiple fields,
I do not know how.

The operator on the floor sees a record with the JobNumber (our in-house
number), the ProcessPlan (again, an in-house number), the PartNumber and the
Quantity, and a listing of Operations associated with the ProcessPlan. The
listing of Operations, in the order in which they are performed (a sequence
number is added when the record is created), is in an unbound list box.
Let's say I have modified the list box SQL to include only the necessary
fields (eliminating the inadvertent redundancy I added). What I need to do
is to click on the appropriate row in the list box, and create a Rework
record that includes the information from that row, so that the Rework
record can be associated with that Job and with a particular Operation.

My question is how best to make that happen, either with an input box or
with a form. If a list box row includes the PlanOpID, the JobID, and the
OpDescription (only the Description is seen) it has enough information to
associate it with all relevant tables. A rework record created with that
same information would likewise be associated with all relevant tables.

I am not committed to a list box, and could use a subform listing if that
would be better, but I believe the list box is a good approach.