Possible Junction Table Issue

My db is for a metal stamping production plant. My db design has the
following:

tblEmployees
w/
EmployeeID
EmployeeName
EmpPosition1
EmpPosition2
EmpPosition3
Setup (yes/no)
Driver (yes/no)
Shift
Department

tblParts
w/
PartID
Operation1 (stamping, braking, shearing, etc.)
Op1Val (a production value per part placed on each operation by our
engineering department - more later).
Operation2
Op2Val
Operation3
Op3Val
Operation4
Op4Val
Operation5
Op5Val

We produce over 2000 various parts with various numbers of operations
with a completely random choice of employee repsonsible for any given
operation of any part with a completely random quantity for any given
part on an entirely random schedule.

We want to track efficiency and production by the employee and the
total plant on a daily basis by shift, department (we have 2), and the
the total daily plant. It would also be worthwhile to have an historic
record to be able to pull reports for the quarter and annually.

So here is my question: Should I use a junction table to collect all
of the daily production table and if so is it possible or even useful
to do a monthly archive of this table in order to keep the operating
size of the database manageable? If a junction table is the answer, I
could really use some advice on how to set it up bearing in mind that
shift supervisors will be using a form to input data for each
employee.

Thanks for your help.

Tim

Re: Possible Junction Table Issue

On part can have many operations.
There is therefore a one-to-many relation between parts and operations:

Operation table (one record per operation):
OperationID
OperationName

Part table (one record per part):
PartID
PartName

PartOperation:
PartID relates to Part.PartID
OperationID relates to Operation.OperationID
StepNum Number (the order of the operations to make the part.)
MinutesEach Number (how many minutes to allow for employee to perform
this step, for each item.)

Now that you have taught Access the steps to make a part, you will produce
many instance of a part over time. Therefore you will have a Production
table like this:


Production (one record per part produced):
ProductionID primary key
PartID relates to Part.PartID
ProductionDate when produced
Quantity how many (may not apply if you need individual
serial numbers.)

You also want to track the operations involved in producing each part
instance, and assign each step to an employee, so you will also need:

ProductOperation (one record for each step in making the part):
ProductionID relates to Production.ProductionID
OperationID relates to Operation.OperationID
EmployeeID employee responsible for this operation on this part
instance.
(Assumes the Employee table as you suggested.)

Once the structure is in place, you can work on the interface. Presumably
you will have a main form bound to Production, with a subform bound
ProductOperation for the steps. You could use the AfterInsert event of the
main form to execute an Append query statement that selects the
PartOperation records for the relevant part, and appends them to the
ProductOperation table. This will (apparently) require the assignment of a
random employee to each step. After executing the query, Requery the subform
to make sure they show up.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1191815969.848475.213980@50g2000hsm.googlegroups.com...
> My db is for a metal stamping production plant. My db design has the
> following:
>
> tblEmployees
> w/
> EmployeeID
> EmployeeName
> EmpPosition1
> EmpPosition2
> EmpPosition3
> Setup (yes/no)
> Driver (yes/no)
> Shift
> Department
>
> tblParts
> w/
> PartID
> Operation1 (stamping, braking, shearing, etc.)
> Op1Val (a production value per part placed on each operation by our
> engineering department - more later).
> Operation2
> Op2Val
> Operation3
> Op3Val
> Operation4
> Op4Val
> Operation5
> Op5Val
>
> We produce over 2000 various parts with various numbers of operations
> with a completely random choice of employee repsonsible for any given
> operation of any part with a completely random quantity for any given
> part on an entirely random schedule.
>
> We want to track efficiency and production by the employee and the
> total plant on a daily basis by shift, department (we have 2), and the
> the total daily plant. It would also be worthwhile to have an historic
> record to be able to pull reports for the quarter and annually.
>
> So here is my question: Should I use a junction table to collect all
> of the daily production table and if so is it possible or even useful
> to do a monthly archive of this table in order to keep the operating
> size of the database manageable? If a junction table is the answer, I
> could really use some advice on how to set it up bearing in mind that
> shift supervisors will be using a form to input data for each
> employee.
>
> Thanks for your help.
>
> Tim

Re: Possible Junction Table Issue

tbrogdon@gmail.com wrote in
news:1191843151.936735.75160@50g2000hsm.googlegroups.com:

> Thank you very much for your response. It helps a bunch.
>
> There is one thing that I am not clear on and it is of course my
> fault for not being clearer initially...One part can have many
> operations but let me give you 2 examples as I am trying to figure
> this logic out as well:
>
> Part number 1055471 has 5 operations - ALL five of them are
> stamping (i.e., Stamp1, Stamp2, Stamp3, etc.)
>
> whereas
>
> Part 1098050 has 4 operations in this order: Stamp1, Turret1,
> Stamp2, Brake1.
>
> Many other parts have similar disjointed orders. How does this
> affect the logic of my tblPartOperation?
>
>
I'm sure Allen will respond when he wakes up, (he's in Australia),
but be assured it handles this easily, because of the partOperation
table as Allen defined it.

PartID -OperationID - Step
1055471-Stamp1 - 1
1055471-Stamp2 - 2
1055471-Stamp3 - 3
1055471-Stamp5 - 4
1055471-Stamp4 - 5
1098050-Stamp1 - 1
1098050-Turret1 -2
1098050-Stamp2 - 3
1098050-Brake1 - 4

I even swapped the order of two operations, just because I decided
to revise the process :-)

Q

>
> On Oct 7, 11:52 pm, "Allen Browne"
> wrote:
>> On part can have many operations.
>> There is therefore a one-to-many relation between parts and
>> operations:
>>
>> Operation table (one record per operation):
>> OperationID
>> OperationName
>>
>> Part table (one record per part):
>> PartID
>> PartName
>>
>> PartOperation:
>> PartID relates to Part.PartID
>> OperationID relates to Operation.OperationID
>> StepNum Number (the order of the operations to make the
>> part.) MinutesEach Number (how many minutes to allow for
>> employee to perform
>> this step, for each item.)
>>
>> Now that you have taught Access the steps to make a part, you
>> will produce many instance of a part over time. Therefore you
>> will have a Production table like this:
>>
>> Production (one record per part produced):
>> ProductionID primary key
>> PartID relates to Part.PartID
>> ProductionDate when produced
>> Quantity how many (may not apply if you need
>> individual
>> serial numbers.)
>>
>> You also want to track the operations involved in producing each
>> part instance, and assign each step to an employee, so you will
>> also need:
>>
>> ProductOperation (one record for each step in making the part):
>> ProductionID relates to Production.ProductionID
>> OperationID relates to Operation.OperationID
>> EmployeeID employee responsible for this operation on
>> this part
>> instance.
>> (Assumes the Employee table as you suggested.)
>>
>> Once the structure is in place, you can work on the interface.
>> Presumably you will have a main form bound to Production, with a
>> subform bound ProductOperation for the steps. You could use the
>> AfterInsert event of the main form to execute an Append query
>> statement that selects the PartOperation records for the relevant
>> part, and appends them to the ProductOperation table. This will
>> (apparently) require the assignment of a random employee to each
>> step. After executing the query, Requery the subform to make sure
>> they show up.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users -http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> wrote in message
>>
>> news:1191815969.848475.213980@50g2000hsm.googlegroups.com...
>>
>>
>>
>> > My db is for a metal stamping production plant. My db design
>> > has the following:
>>
>> > tblEmployees
>> > w/
>> > EmployeeID
>> > EmployeeName
>> > EmpPosition1
>> > EmpPosition2
>> > EmpPosition3
>> > Setup (yes/no)
>> > Driver (yes/no)
>> > Shift
>> > Department
>>
>> > tblParts
>> > w/
>> > PartID
>> > Operation1 (stamping, braking, shearing, etc.)
>> > Op1Val (a production value per part placed on each operation by
>> > our engineering department - more later).
>> > Operation2
>> > Op2Val
>> > Operation3
>> > Op3Val
>> > Operation4
>> > Op4Val
>> > Operation5
>> > Op5Val
>>
>> > We produce over 2000 various parts with various numbers of
>> > operations with a completely random choice of employee
>> > repsonsible for any given operation of any part with a
>> > completely random quantity for any given part on an entirely
>> > random schedule.
>>
>> > We want to track efficiency and production by the employee and
>> > the total plant on a daily basis by shift, department (we have
>> > 2), and the the total daily plant. It would also be worthwhile
>> > to have an historic record to be able to pull reports for the
>> > quarter and annually.
>>
>> > So here is my question: Should I use a junction table to
>> > collect all of the daily production table and if so is it
>> > possible or even useful to do a monthly archive of this table
>> > in order to keep the operating size of the database manageable?
>> > If a junction table is the answer, I could really use some
>> > advice on how to set it up bearing in mind that shift
>> > supervisors will be using a form to input data for each
>> > employee.
>>
>> > Thanks for your help.
>>
>> > Tim- Hide quoted text -
>>
>> - Show quoted text -
>
>
>



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Possible Junction Table Issue

Thank you very much for your response. It helps a bunch.

There is one thing that I am not clear on and it is of course my fault
for not being clearer initially...One part can have many operations
but let me give you 2 examples as I am trying to figure this logic out
as well:

Part number 1055471 has 5 operations - ALL five of them are stamping
(i.e., Stamp1, Stamp2, Stamp3, etc.)

whereas

Part 1098050 has 4 operations in this order: Stamp1, Turret1, Stamp2,
Brake1.

Many other parts have similar disjointed orders. How does this affect
the logic of my tblPartOperation?



On Oct 7, 11:52 pm, "Allen Browne" wrote:
> On part can have many operations.
> There is therefore a one-to-many relation between parts and operations:
>
> Operation table (one record per operation):
> OperationID
> OperationName
>
> Part table (one record per part):
> PartID
> PartName
>
> PartOperation:
> PartID relates to Part.PartID
> OperationID relates to Operation.OperationID
> StepNum Number (the order of the operations to make the part.)
> MinutesEach Number (how many minutes to allow for employee to perform
> this step, for each item.)
>
> Now that you have taught Access the steps to make a part, you will produce
> many instance of a part over time. Therefore you will have a Production
> table like this:
>
> Production (one record per part produced):
> ProductionID primary key
> PartID relates to Part.PartID
> ProductionDate when produced
> Quantity how many (may not apply if you need individual
> serial numbers.)
>
> You also want to track the operations involved in producing each part
> instance, and assign each step to an employee, so you will also need:
>
> ProductOperation (one record for each step in making the part):
> ProductionID relates to Production.ProductionID
> OperationID relates to Operation.OperationID
> EmployeeID employee responsible for this operation on this part
> instance.
> (Assumes the Employee table as you suggested.)
>
> Once the structure is in place, you can work on the interface. Presumably
> you will have a main form bound to Production, with a subform bound
> ProductOperation for the steps. You could use the AfterInsert event of the
> main form to execute an Append query statement that selects the
> PartOperation records for the relevant part, and appends them to the
> ProductOperation table. This will (apparently) require the assignment of a
> random employee to each step. After executing the query, Requery the subform
> to make sure they show up.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> wrote in message
>
> news:1191815969.848475.213980@50g2000hsm.googlegroups.com...
>
>
>
> > My db is for a metal stamping production plant. My db design has the
> > following:
>
> > tblEmployees
> > w/
> > EmployeeID
> > EmployeeName
> > EmpPosition1
> > EmpPosition2
> > EmpPosition3
> > Setup (yes/no)
> > Driver (yes/no)
> > Shift
> > Department
>
> > tblParts
> > w/
> > PartID
> > Operation1 (stamping, braking, shearing, etc.)
> > Op1Val (a production value per part placed on each operation by our
> > engineering department - more later).
> > Operation2
> > Op2Val
> > Operation3
> > Op3Val
> > Operation4
> > Op4Val
> > Operation5
> > Op5Val
>
> > We produce over 2000 various parts with various numbers of operations
> > with a completely random choice of employee repsonsible for any given
> > operation of any part with a completely random quantity for any given
> > part on an entirely random schedule.
>
> > We want to track efficiency and production by the employee and the
> > total plant on a daily basis by shift, department (we have 2), and the
> > the total daily plant. It would also be worthwhile to have an historic
> > record to be able to pull reports for the quarter and annually.
>
> > So here is my question: Should I use a junction table to collect all
> > of the daily production table and if so is it possible or even useful
> > to do a monthly archive of this table in order to keep the operating
> > size of the database manageable? If a junction table is the answer, I
> > could really use some advice on how to set it up bearing in mind that
> > shift supervisors will be using a form to input data for each
> > employee.
>
> > Thanks for your help.
>
> > Tim- Hide quoted text -
>
> - Show quoted text -

Re: Possible Junction Table Issue

Thanks Bob.

So will I have a seperate record for each step of a given part? I am
so sorry that I am unclear on this. It is probably the defining
befuddlement (sic) of my lack of experience! :-)

Thank you again.

Tim

Re: Possible Junction Table Issue

tbrogdon@gmail.com wrote in news:1191850872.100687.325160
@v3g2000hsg.googlegroups.com:

> Thanks Bob.
>
> So will I have a seperate record for each step of a given part? I
am
> so sorry that I am unclear on this. It is probably the defining
> befuddlement (sic) of my lack of experience! :-)
>
> Thank you again.
>
> Tim
>
>
That's correct, you will have a separate record for each step of a
given part, in the PartOperation table, but just one record per part
in the part_Definition table.

I imagine that what you use now is a paper system, with a master
file of part routings (1 routing per part) which has each operation
as a separate line (record in the database). When you make a new
batch of a part, you copy that template, fill in the production run
info, and have the operator fill in each line as it's completed.

In the database, you would follow the same steps. The structure
Allen designed for you handles all that stuff.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Possible Junction Table Issue

Yes: the PartOperation table has a record for each step to make each part.
Use the StepNum field to order the steps.

I'm not really clear whether you used Stamp1 as the name for a particular
type of operation, or whether it means that the first step is a Stamp. If
the former, you will have Stamp1 as a record in the Operation table; if the
latter, you will have Stamp as a record in the Operation table, and then the
StepNum will determine the order of the steps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1191850872.100687.325160@v3g2000hsg.googlegroups.com...
> Thanks Bob.
>
> So will I have a seperate record for each step of a given part? I am
> so sorry that I am unclear on this. It is probably the defining
> befuddlement (sic) of my lack of experience! :-)
>
> Thank you again.
>
> Tim

Re: Possible Junction Table Issue

You guys are giving me great help!

To clarify - I've used Stamp1 above as both BUT in can be different.
For example:

A certain part may have the following operations in this order:

1st operation is a shear job thus: Shear1 (there is never a Shear2)
2nd operation is a stamping procedure: Stamp1
3rd operation is a brake operation: Brake1
4th operation is back to Stamping: Stamp2
5th Operation is back to brakes again: Brake2
6th operation is assembly: Assy1

Another part may be the following:

1st operation is stamping: Stamp1
2nd operation is stamping: Stamp2
3rd operation is Stamping: Stamp3
etc.

Currently (this conversation) I am using this nomenclature to try to
clarify that the operations are of various types and not necessarily
in sequential order among types.

Re: Possible Junction Table Issue

What the below also means is that in tblParts.Operation1 the value
could be either be Stamp1 or Shear1 or Turret1, etc. Following,
tblParts.Operation2 could be Brake1 or Stamp2, etc. Operation3 could
be Brake1 or Brake2 or Stamp2 or anything else. Does this help clarify
my reasoning?

On Oct 8, 10:16 am, tbrog...@gmail.com wrote:
> You guys are giving me great help!
>
> To clarify - I've used Stamp1 above as both BUT in can be different.
> For example:
>
> A certain part may have the following operations in this order:
>
> 1st operation is a shear job thus: Shear1 (there is never a Shear2)
> 2nd operation is a stamping procedure: Stamp1
> 3rd operation is a brake operation: Brake1
> 4th operation is back to Stamping: Stamp2
> 5th Operation is back to brakes again: Brake2
> 6th operation is assembly: Assy1
>
> Another part may be the following:
>
> 1st operation is stamping: Stamp1
> 2nd operation is stamping: Stamp2
> 3rd operation is Stamping: Stamp3
> etc.
>
> Currently (this conversation) I am using this nomenclature to try to
> clarify that the operations are of various types and not necessarily
> in sequential order among types.

Re: Possible Junction Table Issue

You have 2 things here:
- the type of operation (Stamp, Turret, Brake, ...)
- the order of operations (1,2,3, ...)

Since those seem to be 2 different pieces of information, you will want to
use 2 fields, so the table will have data like this:
Step Operation
=== =======
1 Brake
2 Brake
3 Stamp

One of the basic rules of data normalization is to make the fields atomic
(i.e. only store one thing in each field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1191859454.792952.298260@g4g2000hsf.googlegroups.com...
> What the below also means is that in tblParts.Operation1 the value
> could be either be Stamp1 or Shear1 or Turret1, etc. Following,
> tblParts.Operation2 could be Brake1 or Stamp2, etc. Operation3 could
> be Brake1 or Brake2 or Stamp2 or anything else. Does this help clarify
> my reasoning?
>
> On Oct 8, 10:16 am, tbrog...@gmail.com wrote:
>> You guys are giving me great help!
>>
>> To clarify - I've used Stamp1 above as both BUT in can be different.
>> For example:
>>
>> A certain part may have the following operations in this order:
>>
>> 1st operation is a shear job thus: Shear1 (there is never a Shear2)
>> 2nd operation is a stamping procedure: Stamp1
>> 3rd operation is a brake operation: Brake1
>> 4th operation is back to Stamping: Stamp2
>> 5th Operation is back to brakes again: Brake2
>> 6th operation is assembly: Assy1
>>
>> Another part may be the following:
>>
>> 1st operation is stamping: Stamp1
>> 2nd operation is stamping: Stamp2
>> 3rd operation is Stamping: Stamp3
>> etc.
>>
>> Currently (this conversation) I am using this nomenclature to try to
>> clarify that the operations are of various types and not necessarily
>> in sequential order among types.

Re: Possible Junction Table Issue

"I'm sure Allen will respond when he wakes up, (he's in Australia),
but be assured it handles this easily, because of the partOperation
table as Allen defined it.

PartID -OperationID - Step
1055471-Stamp1 - 1
1055471-Stamp2 - 2
1055471-Stamp3 - 3
1055471-Stamp5 - 4
1055471-Stamp4 - 5
1098050-Stamp1 - 1
1098050-Turret1 -2
1098050-Stamp2 - 3
1098050-Brake1 - 4 "

Why doesn't this go against the 2NF - "Remove subsets of data that
apply to multiple rows of a table and place them in separate
tables." ? Is it because the partOperationTable is a junction table or
because I am misinterpreting the 2NF? My original thought was, in
order to avoid redundancy altogether, I would have each PartID be a
unique record with multiple OperationID fields across that record that
would act as "yes/no" or "go/nogo." If the OperationID field gave the
green light to a query, the query would then look for a MinutesEach
value in an associated field by OperationID in the same table.

Can you help me see why I am making this too complicated in my head?


> One of the basic rules of data normalization is to make the fields atomic
> (i.e. only store one thing in each field.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> wrote in message
>
> news:1191859454.792952.298260@g4g2000hsf.googlegroups.com...
>
>
>
> > What the below also means is that in tblParts.Operation1 the value
> > could be either be Stamp1 or Shear1 or Turret1, etc. Following,
> > tblParts.Operation2 could be Brake1 or Stamp2, etc. Operation3 could
> > be Brake1 or Brake2 or Stamp2 or anything else. Does this help clarify
> > my reasoning?
>
> > On Oct 8, 10:16 am, tbrog...@gmail.com wrote:
> >> You guys are giving me great help!
>
> >> To clarify - I've used Stamp1 above as both BUT in can be different.
> >> For example:
>
> >> A certain part may have the following operations in this order:
>
> >> 1st operation is a shear job thus: Shear1 (there is never a Shear2)
> >> 2nd operation is a stamping procedure: Stamp1
> >> 3rd operation is a brake operation: Brake1
> >> 4th operation is back to Stamping: Stamp2
> >> 5th Operation is back to brakes again: Brake2
> >> 6th operation is assembly: Assy1
>
> >> Another part may be the following:
>
> >> 1st operation is stamping: Stamp1
> >> 2nd operation is stamping: Stamp2
> >> 3rd operation is Stamping: Stamp3
> >> etc.
>
> >> Currently (this conversation) I am using this nomenclature to try to
> >> clarify that the operations are of various types and not necessarily
> >> in sequential order among types.- Hide quoted text -
>
> - Show quoted text -

Re: Possible Junction Table Issue

I have a problem with the following. I found an exception.
> PartID -OperationID - Step
> 1055471-Stamp1 - 1
> 1055471-Stamp2 - 2
> 1055471-Stamp3 - 3
> 1055471-Stamp5 - 4
> 1055471-Stamp4 - 5
This one is easy. It has no shear job or interruption from the press
whereas:

> 1098050-Stamp1 - 1
> 1098050-Turret1 -2
> 1098050-Stamp2 - 3
> 1098050-Brake1 - 4

The problem is that when the supervisors do their data input at the
end of the shift they don't know the turret operation above as the 2nd
op. They know it as the 1st turret op. The same for the 4th op of
1098050. The supervisors know that as the 1st brake operation not as
the 4th op of 1098050.

Do I need to redesign my table layouts or is there a workaround that I
am unfamiliar with?

Thanks,

Tim

Re: Possible Junction Table Issue

I have run into a problem with the following - actually the logic for
1055471 will work just fine as there are no other "types" of
operations involved (e.g., shear, turret, etc).

> PartID -OperationID - Step
> 1055471-Stamp1 - 1
> 1055471-Stamp2 - 2
> 1055471-Stamp3 - 3
> 1055471-Stamp5 - 4
> 1055471-Stamp4 - 5

But 1098050 is different.

> 1098050-Stamp1 - 1
> 1098050-Turret1 -2
> 1098050-Stamp2 - 3
> 1098050-Brake1 - 4 "

The problem is going to be with the other supervisors who input data
at the end of each shift. For example, they don't know the Turret1
operation as the 2nd operation of 1098050. Thye know it as the 1st
turret operation. Likewise, they don't know the 2nd stamp operation
(Stamp2) as the 3rd operation. They know it as the the 2nd stamp
operation which is quite different than the 3rd operation of 1098050.

Do I need to rethink my table designs and relationships or is there a
workaround or logic that I am unfamiliar with?

Thanks in advance,

Tim

Re: Possible Junction Table Issue

Perhaps I've not understood you.

From your previous instructions, I understood that a Stamp1 and Stamp2 are
actually the same operation, i.e. the numerical suffix indicates only the
fact that the Stamp2 is the 2nd stamp operation. If a Stamp2 is a different
kind of operation than a Stamp1, then you do validly need to store them as
different operations.

Your example of the 1098050 still needs some kind of field that indicates
the order of the operations though.

If a Stamp2 and Stamp1 are in fact the same kind of operation, then you can
use the Step field to determine which one is the 2nd stamping operation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1192590275.016276.173990@v23g2000prn.googlegroups.com.. .
>
> I have run into a problem with the following - actually the logic for
> 1055471 will work just fine as there are no other "types" of
> operations involved (e.g., shear, turret, etc).
>
>> PartID -OperationID - Step
>> 1055471-Stamp1 - 1
>> 1055471-Stamp2 - 2
>> 1055471-Stamp3 - 3
>> 1055471-Stamp5 - 4
>> 1055471-Stamp4 - 5
>
> But 1098050 is different.
>
>> 1098050-Stamp1 - 1
>> 1098050-Turret1 -2
>> 1098050-Stamp2 - 3
>> 1098050-Brake1 - 4 "
>
> The problem is going to be with the other supervisors who input data
> at the end of each shift. For example, they don't know the Turret1
> operation as the 2nd operation of 1098050. Thye know it as the 1st
> turret operation. Likewise, they don't know the 2nd stamp operation
> (Stamp2) as the 3rd operation. They know it as the the 2nd stamp
> operation which is quite different than the 3rd operation of 1098050.
>
> Do I need to rethink my table designs and relationships or is there a
> workaround or logic that I am unfamiliar with?
>
> Thanks in advance,
>
> Tim

Re: Possible Junction Table Issue

Allen,

Thank you for your perserverance.

1098050:

The 1st operation (Stamp1) entails loading a die in a stamping press
and stamping e.g., 100 parts. The 2nd operation (Turret1) takes those
100 parts to a different dept. which considers Turret1 as that dept.'s
1st op; places the 100 parts on a turret and cuts a hole. The 3rd
operation returns the 100 parts to the stamping dept. (Stamp2) to be
stamped in another die - the second op for that dept. The 4th
operation returns the parts to the other dept again (Brake1) but it is
not recorded as the second op for that dept. It is recorded as the 1st
brake op of 1098050.

The supervisor in the Stamping dept. will record 2 operations: Stamp1,
and Stamp2 in his daily production report - even though these are
literally the first and third ops. The supervisor for the other dept.
will record one op on the turret (Turret1) and one op on the brake
(Brake1) on his production sheet - again - even though these are
literally the 2nd and 4th ops.

Some parts are produced in literal order (the 1054471 example above).
Many are not and cross back and forth between departments and more
importantly through different *types* of operations. Thus the
*literal* numerical sequence is really never recorded by the floor
supervisor unless the part is produced entirely through a single
process (i.e., entirely through subsequent dies in a press).

Your feedback, as always, is much appreciated.

Tim

Re: Possible Junction Table Issue

You are closer to the data than we are, and so you will understand better
what is needed.

It seems to me that Stamp is one kind of entity (i.e. the department where
the operation occurs), and that the number is another kind of entity (i.e.
the occurance of the operation of that department.) To make this data
atomic, this should therefore be 2 fields.

If you believe it best, you can make the 2nd field (the Step number)
specific to that department if you wish. I don't think I would be satisfied
with that, as it does not adequately define the order of the steps. That is,
there is no way to look at the data:
Stamp 1
Turret 1
Stamp 2
Stamp 3
Turret 2
and figure out the actual order of operations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1193079172.440977.39300@z24g2000prh.googlegroups.com...
> Allen,
>
> Thank you for your perserverance.
>
> 1098050:
>
> The 1st operation (Stamp1) entails loading a die in a stamping press
> and stamping e.g., 100 parts. The 2nd operation (Turret1) takes those
> 100 parts to a different dept. which considers Turret1 as that dept.'s
> 1st op; places the 100 parts on a turret and cuts a hole. The 3rd
> operation returns the 100 parts to the stamping dept. (Stamp2) to be
> stamped in another die - the second op for that dept. The 4th
> operation returns the parts to the other dept again (Brake1) but it is
> not recorded as the second op for that dept. It is recorded as the 1st
> brake op of 1098050.
>
> The supervisor in the Stamping dept. will record 2 operations: Stamp1,
> and Stamp2 in his daily production report - even though these are
> literally the first and third ops. The supervisor for the other dept.
> will record one op on the turret (Turret1) and one op on the brake
> (Brake1) on his production sheet - again - even though these are
> literally the 2nd and 4th ops.
>
> Some parts are produced in literal order (the 1054471 example above).
> Many are not and cross back and forth between departments and more
> importantly through different *types* of operations. Thus the
> *literal* numerical sequence is really never recorded by the floor
> supervisor unless the part is produced entirely through a single
> process (i.e., entirely through subsequent dies in a press).
>
> Your feedback, as always, is much appreciated.
>
> Tim
>

Re: Possible Junction Table Issue

>
> If you believe it best, you can make the 2nd field (the Step number)
> specific to that department if you wish. I don't think I would be satisfied
> with that, as it does not adequately define the order of the steps. That is,
> there is no way to look at the data:
> Stamp 1
> Turret 1
> Stamp 2
> Stamp 3
> Turret 2
> and figure out the actual order of operations.
>

I see the value of your observation above. While we don't on a daily
basis utilize the actual order of operations, I am sure at some
unknown point in the future the question will arise. Thus I am
considering having a third field for each part that denotes the -
actual- order of operations as well as the field which describes the
Step number by department:

OperationID - OperationStepNum - PartStepNum (the literal process
step)
Stamp 1 1
Turret 1 2
Stamp 2 3
Stamp 3 4
Turret 2 5

What do you think?

Re: Possible Junction Table Issue

Yes, that solves the issue of knowing the actual steps, but you have now
introduced the possibilty that the data is inconsistent. For example:
Stamp 1 1
Turret 1 2
Stamp 2 4
Stamp 3 3
Turret 2 5

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1193147865.928962.202990@y27g2000pre.googlegroups.com.. .
>
>>
>> If you believe it best, you can make the 2nd field (the Step number)
>> specific to that department if you wish. I don't think I would be
>> satisfied
>> with that, as it does not adequately define the order of the steps. That
>> is,
>> there is no way to look at the data:
>> Stamp 1
>> Turret 1
>> Stamp 2
>> Stamp 3
>> Turret 2
>> and figure out the actual order of operations.
>>
>
> I see the value of your observation above. While we don't on a daily
> basis utilize the actual order of operations, I am sure at some
> unknown point in the future the question will arise. Thus I am
> considering having a third field for each part that denotes the -
> actual- order of operations as well as the field which describes the
> Step number by department:
>
> OperationID - OperationStepNum - PartStepNum (the literal process
> step)
> Stamp 1 1
> Turret 1 2
> Stamp 2 3
> Stamp 3 4
> Turret 2 5
>
> What do you think?

Re: Possible Junction Table Issue

> Yes, that solves the issue of knowing the actual steps, but you have now
> introduced the possibilty that the data is inconsistent. For example:
> Stamp 1 1
> Turret 1 2
> Stamp 2 4
> Stamp 3 3
> Turret 2 5


Fortunately, WITHIN types of operations (e.g., stamping), the order is
always sequential. In other words, Stamp3 never comes before Stamp1 or
Stamp2. The same is true for Brakes and Turrets.

Given that, do you forsee any other possible pitfalls?

Re: Possible Junction Table Issue

The point is that having both the OperationStepNum and the PartStepNum
fields involves storing redundant data. This breaks basic normalization
rules, and the reason is that the data can be inconsistent. As in the
obviously bad example, you are open to the case where the data is nonsense.

So, OperationStepNum is inadequate (does not describe the steps adequately),
and the combination of OperationStepNum and PartStepNum is not normalized
either.

I'll leave it up to you to decide how to create a correctly normalized
structure. It seems to me that the combinaton of OperationID + PartStepNum
is the adequate, normalized solution, even though it is not how the
individual departments think about their work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

wrote in message
news:1193153725.194792.241680@k35g2000prh.googlegroups.com.. .
>
>> Yes, that solves the issue of knowing the actual steps, but you have now
>> introduced the possibilty that the data is inconsistent. For example:
>> Stamp 1 1
>> Turret 1 2
>> Stamp 2 4
>> Stamp 3 3
>> Turret 2 5
>
>
> Fortunately, WITHIN types of operations (e.g., stamping), the order is
> always sequential. In other words, Stamp3 never comes before Stamp1 or
> Stamp2. The same is true for Brakes and Turrets.
>
> Given that, do you forsee any other possible pitfalls?

Re: Possible Junction Table Issue

Allen,

I am very grateful for all of your help. I indeed see the issue and
value of solving the normalization question. I may be able to find a
legitimate workaround for that.

Thank you again for all of your help!

Tim