How To Handle New Random Calculations

[tbrogdon]

One of my main reports calculates the total number of hours worked by
180 employees across 3 shifts and 2 departments every day multiplied
by a constant stored value for each part produced. This would be
relatively easy if each employee actually worked 8 hours and each
shift were actually 8 hours long - but of course they are not.

Two of the shifts are 8 hours long and the third shift is 7.5 hours
long. On top of that, we have a lot of temp help which means that the
number of hours worked by a given employee varies wildly also.

I have tblProduction which contains these fields: ProdDate, Dept, and
Shift. Then of course I have tblShift, tblDept, tblParts, and
tblEmployees. I also currently have tblEmployeeHoursAdjust. The
intended purpose of this table was to capture changes in the number of
hours an employee works BUT this is only a handful of employees a day.
It does matter greatly that I capture those changes however.

The basic equation for our calculation is (PieceValueOfAPart/
TotalHoursWorkedByShiftAndDept).

Has anyone had similar hurdles?

Thanks,

Tim

 

[Dale Fye]

Tim,

Need a little bit more info regarding the fields and relationships between
fields in these tables.

If I understand you correctly, each department produces certain parts, which
have a PartPieceValue. For each department/shift, you want to calculate the
number of each part produced * the PartPieceValue, and divide that by the
total number of hours worked by the shift. Is that accurate?

If so, where are you storing the production numbers for each of the
department/shifts, and where are you storing the information on workers hours?

Dale

 

[tbrogdon]

Hi Dale,

I've included more detail. I appreciate the help.

If I understand you correctly, each department produces certain parts, which
have a PartPieceValue. For each department/shift, you want to calculate the
number of each part produced * the PartPieceValue, and divide that by the
total number of hours worked by the shift. Is that accurate?

That is correct. I will also want to calculate the entire plant on a
daily basis and by Employee on a daily/weekly basis.

If so, where are you storing the production numbers for each of the
department/shifts, and where are you storing the information on workers hours?

Here is a synopsis of my structure:
1) tblProduction w/ ProdDate, Department, and Shift (these 3 are a
composite PK)
2) tblProductionOperation w/ ProdDate, Department, Shift (all three
fields are linked to tblProduction), PartID, OperationStepNumber,
QtyRun, QtyScrap, WorkstationID, Operator1 (instance of EmployeeID),
Operator2 (instance of EmployeeID)
3) tblDepartment w/ Department (pk) - There are two departments.
4) tblShift w/ Shift (pk) - there are three shifts.
5) tblPart w/ PartID (pk)
6) tblPartOperation w/ PartOperationID (pk), PartID, OperationID (some
parts have multiple operations), StepNumber, PartPieceValue (this a
numeric value that is multiplied by the number of parts run)
7) tblWorkstation w/ WorkstationID (pk), Department, OperationID
8) tblEmployees w/ EmployeeID (pk), Firstname, LastName, Department,
Shift, PrimaryFunction
9) tblEmployeeHoursAdjust w/ 4 field composite pk (EmployeeID,
ProdDate, Department, Shift) and TotalHoursWorked (this is a numeric
value that is divided into PartPieceValue to give a Productivity
percentage for the employee/shift/day/week).

Currently I am not storing the hours worked by an individual employee
anywhere - except it just occurred to me that I should be doing that
in tblEmployeeHoursAdjust. I was just going to use this for employees
with modified hours but I could just store all employees in there and
create a form that accepts modifications to the default shift/hour
values stored in the table I already have. Duh...Sometimes I just need
to think (write) it through! So tblEmployeeHoursAdjust is now going to
contain the hours worked for all employees by ProdDate, Department,
and Shift. I will have a new form that the user utilizes to adjust
individual employees hours that vary from each shift's default number
of hours.

So...once I've done that, do you have any suggestions on how I should
approach my calculations?

Thank you,
Tim

 

[Dale Fye]

Tim,

I'm not sure how this relates to "New Random Calculations", but here goes.

Why do you have two OperatorID values in [tblProduction Operation]? Are two
operators required to work on some parts to complete it? If so, will the
individual that is performing Operator2 be in that position during the
entire shift? Generally, it is bad normalization technique to do this. If
you have multiple operators for a particular operation, it is generally
better to have only a single OperatorID field, and have multiple records to
when multiple people are required for that task. To resolve this, you might
want to create a query (qry_ProductionOperation) that looks like:

SELECT ProdDate, Department, Shift, PartID, OperationStepNumber, QtyRun,
QtyScrap, WorkstationID, Operator1 as OperatorID
FROM tblProductionOperation
UNION ALL
SELECT ProdDate, Department, Shift, PartID, OperationStepNumber, QtyRun,
QtyScrap, WorkstationID, Operator2 as OperatorID
FROM tblProductionOperation
WHERE Operator2 IS NOT NULL

The down side of this is that you can no longer just sum the QtyRun and
QtyScrap fields, because you have two records for some
PartID/OperationStepNumber combinations. To resolve this, if multiple
personnel are required to perform a particular PartID/Operation/StepNumber,
I would probably add a field to the PartOperation table that indicates the
number of operators required. With that info, you could join those two
tables and compute the per person throughput
(qtyRun-qtyScrap)/ReqNumOperators, then you could sum across those values
without double counting that production.

In tblProductionOperation, you have one field (OperationStepNumber), how
does this one field correlate with the two fields (OperationID, StepNumber)
in tblPartOperation? If OperationStepNumber is a concatenated field or some
calculation based on the other two, then , I recommend that you break it
into its parts (OperationID, and StepNumber) to make it easier to join these
tables.

To start with, I think you will need to add the one query
(qry_ProductionOperation) and two tables to your query grid
(tblPartOperation, and tblEmployeeHours). Am I correct in my assessment
that there is a PartPieceValue for each part/step, or is there a just a
single value for each part? If the latter, then I think the PartPieceValue
field belongs in the tblPart, not tblPartOperation. If the former, then you
will need to join qry_ProductionOperation to tblPartOperation on the PartID,
and Operation/StepNumber fields. Then, you will need to join
qry_ProductionOperation to tblEmployeeHours on the OperatorID = EmployeeID
and ProdDate fields.

From there, I think you can bring in the ProdDate, Dept, Shift,
SUM((QtyRun-QtyScrap)*PartPieceValue/ReqNumOperators) as Productivity,
Sum([HoursWorked]) as TotHours, Productivity/TotHours as ProdPerHour, and
finally GROUP BY ProdDate, Dept, Shift

HTH
Dale

 

[tbrogdon]

I'm not sure how this relates to "New Random Calculations". Why do you have two OperatorID values in [tblProduction Operation]? Are two
operators required to work on some parts to complete it? If so, will the
individual that is performing Operator2 be in that position during the
entire shift?

I can explain the subject and your first question about the 2
OperatorID fields at the same time - Any employee on any shift might
work at as many as 5 or 6 Workstations alone or with a partner; they
may work on 1 part all shift or they might work on 5 operations of a
single part or they might work on several completely different parts
singly or with a partner. This varies wildly. And there is no "lead"
operator with a helper - for efficiency report considerations they
are all considered equal so I have to report each employee for any
given part operation. And I need all of those employee names
associated with each part operation at each workstation.

In tblProductionOperation, you have one field (OperationStepNumber), how
does this one field correlate with the two fields (OperationID, StepNumber)
in tblPartOperation? If OperationStepNumber is a concatenated field or some
calculation based on the other two, then , I recommend that you break it
into its parts (OperationID, and StepNumber) to make it easier to join these
tables.

OperationStepNumber is not concatenated - I'll explain that in a
moment.

First of all bear in mind that tblProductionOperation is where 90% of
the data is collected. The majority of tables are lookup tables used
exclusively for reference.

This is probably the most difficult part to describe: There is no
standard, pat process that covers how ALL parts are produced.
OperationID denotes whether a given "step" of the production process
for a given part takes place at the shear; the turrets, the brakes,
the presses, the roll form, etc. Also, some parts cross between
departments for their various operations (e.g., 1084122: Dept.1 -
Shear; Dept.1 Press; Dept.2 Turret; Dept.1 Press- in that order) which
leads me to the most confusing part of this - You can count 4
processes in my example but institutionally we don't count it that
way. We count 3 ops for Dept1 (but they are not 1,2,3 ) and 1 for
Dept2 - and we are all very used to doing it that way. And that is
just one of many variations on a theme. So what we have done in our
pre-existing db of Parts and subsequent PartPieceValue is break it
down by type of workstation which are delimited by department - i.e.,
all turrets are in Dept2; all presses are in Dept1, etc.

So....what I have is tblPart w/ PartID which contains a single record
for each part that we produce. tblPartOperation has multiple record
entries for most parts. A record becomes unique in tblPartOperation
across PartID, OperationID, and StepNum (number which could be the
first, second, third, etc. operation at a press, brake, turret, etc.
And yes each part can have multiple operations with a unique
PartPieceValue associated with each operation. So going back to my
example above you would have Shear1, Turret1, Press1, Press2.

So to revisit tblProductionOperation:
tblProductionOperation.WorkstationID references
tblOperation.OperationID through tblWorkstation.OperationID which
tells me if an operation took place at a shear, press, assembly,
brake, etc. tblProductionOperation.OperationStepNum is not linked to
anything. It is a field that the user inputs a number to represent
whether a given operation at a workstation was the 1st, 2nd, etc. step
for THAT TYPE of workstation (i.e., Press1 (entered as a 1), Press2
(entered as a 2), Press3). However, that numeric value
(OperationStepNum) will reference directly to a value stored in
tblPartOperation.StepNum which subsequently has a corresponding
PartPieceValue.

To be clearer, a given record for a part will record the Workstation
(OperationID), OperationStepNum (StepNum), Operator1, Operator2,
QtyRun, QtyScrap. So we will get for example:

Workstation: S017 (which lets me also reference "Shear" from
OperationID; also tells me the value of Dept via tblDepartment- - in
this case Hard Tool)
PartID: 1084122; literal part number
OperationStepNum: 1 (which will reference a record in PartOperation
for Shear; 1st operation)

Workstation: H003 (which lets me also reference "Press" from
OperationID - still Hard Tool)
PartID: 1084122
OperationStepNum: 1 (which will reference a record in PartOperation
for Press; 1st operation)

Workstation: S005 (turret - the first operation in the other Dept. -
Soft Tool)
PartID 1084122
OperationStepNum: 1 (which will reference a record in PartOperation
for Turret; 1st operation)

Workstation: H005 (Press again - and back to Hard Tool)
PartID 1084122
OperationStepNum: 2 (which will reference a record in PartOperation
for Press; 2nd operation)

Some parts have as many as seven operations each one having a unique
value for tblPartOperation.PartPieceValue.

Does this shed any light?

Thanks,

Tim