Key Violations My Wits End

[tbrogdon]

I have an append query that worked perfectly up until an hour ago
when I decided to fiddle with the primary key of the destination table
and now I am receiving the dreaded key violation warning.

Here is my sql:

PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime;
INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate,
Department, Shift, JobFunctionID )
SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS
ProductionDate, Employees.Department, Employees.Shift,
Employees.JobFunctionID
FROM Employees
WHERE (((Employees.Department)=forms!frmSetEmpHours!cboDept) And
((Employees.Shift)=forms!frmSetEmpHours!cboShift));

Table EmployeeProduction has fields: EmployeeID, ProductionDate,
Department, Shift, JobFunctionID, HoursMachine, and HoursAssembly. The
primary key is a combination of EmployeeID, ProductionDate,
Department, and Shift.

The Employees table is linked to EmployeeProduction on EmployeeID,
Department, Shift, and JobfunctionID.

Everything I have read says to look for the No Duplicates property set
to Yes which none of them in EmployeeProduction are set to yes.

As I said earlier, until I fiddled with it it worked beautifully and
now I have no idea what I have done or how to fix it.
I am at my wit's end. Will someone please help?

Thank you so much

 

[tbrogdon]

I should also add that there are no records at all in
EmployeeProduction and the only autonumber field is
Employees.EmployeeID.

Thank you,

Tim

 

[Ken Snell \(MVP\)]

Define "fiddled with".... what did you change?

Any particular reason you're linking the EmployeeProduction table to the
Employees table by four fields? Shouldn't EmployeeID be sufficient?

 

[tbrogdon]

Hi Ken,

Since my last post I have found the problem. "Fiddled" with means that
the original primary key for EmployeeProduction was a composite key
made up of four fields. What I discovered was that in yet a 3rd table,
Production, the main key for the db was were the violation was
occuring. The primary key for Production -was- a composite of
ProductionDate, Department, and Shift fields. I was "fiddling" with
making a single field as the primary key (i.e., ProductionID) and then
created a unique secondary index on the other three fields which had
been the composite.

I "fixed"it all. Made a backup and am now "fiddling" again as I now
believe that a single primary key is the way to go in both table
Production and in table EmployeeProduction (thus EmpProdID).

However, in my newness to Access this decision leaves me with a new
question: One of the main tasks for each supervisor (user) is to enter
daily shift data for each employee which is defined by ProductionDate,
Department, and Shift. So any time they perform virtually any task
they have to enter data for those three fields.

I had been using frmEmpProd to enter default data in table
EmployeeProduction before but I don't know how to do that now that I
have changed keys. Before frmEmpProd had 3 unbound fields txtDate,
cboDept, and cboShift. The user entered data in those fields and
clicked a button which ran both an Append query and an Update query
back to back and populated table EmployeeProduction with all data
including txtDate AS ProductionDate, cboDept AS Department, and
cboShift AS Shift, EmployeeID, and JobFunctionID. The update query
then checked the value of JobFunctionID in each record and populated
the default values of HoursMachine and HoursAssembly.

The most immediate question I have is this - given the following NEW
table structures:

Production w/ ProductionID (PK) ProductionDate, Department, Shift
Employees w/ EmployeeID (PK), Department, Shift, and JobFunctionID
EmployeeProduction w/ EmpProdID (PK), ProductionID, EmployeeID,
HoursMachine, and HoursAssembly
Shift w/ Shift (PK), ShiftHours
Department w/ Department (PK)

what do I need to do to perform a similar set of operations?
Basically, at the beginning of the shift, the user wants to "setup"
their data for the shift (i.e., pre-populate). So he/she enters the
date, department, and shift they will be entering data for in a form
and when they click the "enter" button I want to not only populate the
correct fields in table Production but also make a new record for
every employee for that department/shift combination in table
EmployeeProduction and include the correct value of ProductionID for
every one as well.

Thank you for your help,

Tim

 

[Ken Snell \(MVP\)]

Comments inline...
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Since my last post I have found the problem.

Good to read this.

"Fiddled" with means that
the original primary key for EmployeeProduction was a composite key
made up of four fields. What I discovered was that in yet a 3rd table,
Production, the main key for the db was were the violation was
occuring. The primary key for Production -was- a composite of
ProductionDate, Department, and Shift fields. I was "fiddling" with
making a single field as the primary key (i.e., ProductionID) and then
created a unique secondary index on the other three fields which had
been the composite.

It's not always necessary, nor always desired, to have a single field for
the primary key. Using a composite primary key (more than one field in
combination) can be useful in many situations -- although, depending upon
the data, they can be a bit trickier to update and use.

I "fixed"it all. Made a backup and am now "fiddling" again as I now
believe that a single primary key is the way to go in both table
Production and in table EmployeeProduction (thus EmpProdID).

However, in my newness to Access this decision leaves me with a new
question: One of the main tasks for each supervisor (user) is to enter
daily shift data for each employee which is defined by ProductionDate,
Department, and Shift. So any time they perform virtually any task
they have to enter data for those three fields.

I had been using frmEmpProd to enter default data in table
EmployeeProduction before but I don't know how to do that now that I
have changed keys. Before frmEmpProd had 3 unbound fields txtDate,
cboDept, and cboShift. The user entered data in those fields and
clicked a button which ran both an Append query and an Update query
back to back and populated table EmployeeProduction with all data
including txtDate AS ProductionDate, cboDept AS Department, and
cboShift AS Shift, EmployeeID, and JobFunctionID. The update query
then checked the value of JobFunctionID in each record and populated
the default values of HoursMachine and HoursAssembly.

You should be able to use the same setup as before... changing the structure
of the primary key does not affect this setup that you've been using. If
you're now using an autonumber field as the primary key, it'll be
automatically "generated" when a new record is created in the table.

However, you might find it easier to use a composite primary key in this
case because you could put the EmployeeProductions table in a subform within
the frmEmpProd form, and then use the three controls (txtDate, cboDept, and
cboShift) as the fields for the subform control's LinkMasterFields
property -- and then use the corresponding fields in the LinkChildFields
property. In this way, the subform would automatically insert the desired
values into those fields without having to run any external queries.

The most immediate question I have is this - given the following NEW
table structures:

Production w/ ProductionID (PK) ProductionDate, Department, Shift
Employees w/ EmployeeID (PK), Department, Shift, and JobFunctionID
EmployeeProduction w/ EmpProdID (PK), ProductionID, EmployeeID,
HoursMachine, and HoursAssembly
Shift w/ Shift (PK), ShiftHours
Department w/ Department (PK)

what do I need to do to perform a similar set of operations?
Basically, at the beginning of the shift, the user wants to "setup"
their data for the shift (i.e., pre-populate). So he/she enters the
date, department, and shift they will be entering data for in a form
and when they click the "enter" button I want to not only populate the
correct fields in table Production but also make a new record for
every employee for that department/shift combination in table
EmployeeProduction and include the correct value of ProductionID for
every one as well.

Ahh, this would change slightly what I suggested above -- using the three
controls as linking fields. In this case, you could run the same queries
that you were running before, with the addition of one table that will let
you "get" the names of employees on the selected shift. The append query
could use this information to then add one record for each shift employee.

Overall, changing your primary key structure should not cause you to
drastically change your current setup. (There are many ways to skin a cat,
and to design a workable database.) If you can post more details about your
other tables, in view of what I'm suggesting here, I'm sure we can provide
specific suggestions to you.

 

[tbrogdon]

Hi Ken,

The db with the composite PK is as follows:
I have tblProduction w/ a composite key consisting of the following 3
fields:
ProductionDate
Department
Shift

tblEmployeeProduction w/
EmpProdID (PK autonumber)
ProductionDate
Department
Shift
EmployeeID
JobFunctionID (default job classification - linked to
tblJobFunction.JobFunctionID)
HoursMachine ( time spent per instance of ProductionID operating
machine - will be input by supervisor each shift )
HoursAssembly ( time spent per instance of ProductionID in assembly -
will be input by supervisor for each shift)
'This table is currently not linked to tblProduction and I feel it
should. I think I am inputting ProductionDate, Dept, and Shift twice
since the two tables are not directly linked. I have expounded on this
below.


tblProductOperation w/
ProdOpID (PK - autonumber)
ProductionDate
Department
Shift
WorkstationID (can be either any number of machines or assembly
areas)
PartID
Setup (can be any employee - linked to tblEmployees on EmployeeID)
Operator1 (can be any employee with any JobFunctionID - linked to
tblEmployees on EmployeeID)
Operator2 (can be any employee with any JobFunctionID - linked to
tblEmployees on EmployeeID)
OperationStepNum (different parts have various amounts of steps to
complete and can be run by a variety of operators on different shifts
in different departments)
QuantityRun


tblEmployees w/
EmployeeID (PK - autonumber)
FirstName
LastName
Department
Shift
JobFunctionID ( can be either 1 for Operator OR 2 for Assembler)


tblShift w/
Shift (PK)
ShiftHours (deafult hours: 1st and 2nd shift are 8 hours, 3rd shift
is
7.5 hours)


tblDepartment w/
Department (PK)


tblParts w/
PartID (PK)
CSH (stands for cost standard hours which is a value per part
produced used to calculate productivity)

tblWorkstation w/
WorkstationID (PK)
Department

tblJobFunctionID w/
JobFunctionID (PK)
JobFunction


I need to track each employees productivity for every part they
produce for each instance of ProductionID. I also need to capture how
many hours each employee works for every instance of ProductionID (any
employee can work more or less ShiftHours due to illness, overtime,
etc. and they can split their time on JobFunction)

The supervisor needs to enter default data at the beginning of the
shift for all employees for that day, dept, shift combination. I have
an append query and an update query attached to a form that has
worked by taking a date value, dept. name, and shift number from a
form and prepopulating tblEmployeeProduction with the default number
of hours an employee should work that day determined by whether their
JobFunctionID is a 1 (Operator) or 2 (Assembler) and secondly which
shift they work on as the number of hours for each shift varies. Then
at the end of shift the supervisor pulls that same data back out of
tblEmployeeProduction to make adjustments (e.g., an employee goes home
early or splits their time between Operator and Assembler).

My only reason for changing to a single PK was in response to being
unable to figure out how to link tblProduction to
tblEmployeeProduction. Somehow, and I confess I have forgotten how, I
successfully linked tblProduction with tblProductionOperation and have
forms and queries that work well with that configuration. I need to
link all three tables I would think since they are completely related
to each other. In other words, I suspect that even though I have Date,
Dept, and Shift recorded in both tables that I won't be able to
relate, query, report data from the 3 main tables if
tblEmployeeProduction is not linked to tblProduction via the composite
key.

But linking those 3 fields from tblProduction(1) to
tblEmployeeProduction (m) results in this message: "No unique index
found for the referenced field of the primary table."

The primary key for tblEmployeeProduction is an autonumber field
[EmpProdID] and I have an index on ProductionDate, Department, and
Shift. I am attempting to enforce referential integrity.

Question: Do I have these tables setup appropriately to capture the
info I need and if not what should I do differently? Specifically, how
should I link or design tblProduction, tblEmployeeProduction, and
tblProductionOperation to best capture the data I need?

I guess I've gone on enough and I hope this is clearer and not more
confusing.

Thank you as always,

Tim

 

[Ken Snell \(MVP\)]

Sorry for the delaying in posting a reply, I've been traveling. I'll post a
reply as quickly as I can.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The db with the composite PK is as follows:
I have tblProduction w/ a composite key consisting of the following 3
fields:
ProductionDate
Department
Shift

< snipped >

 

[Ken Snell \(MVP\)]

Comments inline...
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

The db with the composite PK is as follows:
I have tblProduction w/ a composite key consisting of the following 3
fields:
ProductionDate
Department
Shift

tblEmployeeProduction w/
EmpProdID (PK autonumber)
ProductionDate
Department
Shift
EmployeeID
JobFunctionID (default job classification - linked to
tblJobFunction.JobFunctionID)
HoursMachine ( time spent per instance of ProductionID operating
machine - will be input by supervisor each shift )
HoursAssembly ( time spent per instance of ProductionID in assembly -
will be input by supervisor for each shift)
'This table is currently not linked to tblProduction and I feel it
should. I think I am inputting ProductionDate, Dept, and Shift twice
since the two tables are not directly linked. I have expounded on this
below.

You can create a relationship between the above two tables, using the
ProductionDate, Dept, and Shift fields in both tables, by opening the
Relationships window, clicking on the tblProduction table (add it to the
view if it's not there), hold down the Ctrl key and click on the
ProductionDate, Dept, and Shift fields in succession in that table. Then
drag those fields onto the tblEmployeeProduction table, and you'll get a
"composite relationship" that uses all three fields.

tblProductOperation w/
ProdOpID (PK - autonumber)
ProductionDate
Department
Shift
WorkstationID (can be either any number of machines or assembly
areas)
PartID
Setup (can be any employee - linked to tblEmployees on EmployeeID)
Operator1 (can be any employee with any JobFunctionID - linked to
tblEmployees on EmployeeID)
Operator2 (can be any employee with any JobFunctionID - linked to
tblEmployees on EmployeeID)
OperationStepNum (different parts have various amounts of steps to
complete and can be run by a variety of operators on different shifts
in different departments)
QuantityRun


tblEmployees w/
EmployeeID (PK - autonumber)
FirstName
LastName
Department
Shift
JobFunctionID ( can be either 1 for Operator OR 2 for Assembler)


tblShift w/
Shift (PK)
ShiftHours (deafult hours: 1st and 2nd shift are 8 hours, 3rd shift
is
7.5 hours)


tblDepartment w/
Department (PK)


tblParts w/
PartID (PK)
CSH (stands for cost standard hours which is a value per part
produced used to calculate productivity)

tblWorkstation w/
WorkstationID (PK)
Department

tblJobFunctionID w/
JobFunctionID (PK)
JobFunction


I need to track each employees productivity for every part they
produce for each instance of ProductionID. I also need to capture how
many hours each employee works for every instance of ProductionID (any
employee can work more or less ShiftHours due to illness, overtime,
etc. and they can split their time on JobFunction)

The supervisor needs to enter default data at the beginning of the
shift for all employees for that day, dept, shift combination. I have
an append query and an update query attached to a form that has
worked by taking a date value, dept. name, and shift number from a
form and prepopulating tblEmployeeProduction with the default number
of hours an employee should work that day determined by whether their
JobFunctionID is a 1 (Operator) or 2 (Assembler) and secondly which
shift they work on as the number of hours for each shift varies. Then
at the end of shift the supervisor pulls that same data back out of
tblEmployeeProduction to make adjustments (e.g., an employee goes home
early or splits their time between Operator and Assembler).

My only reason for changing to a single PK was in response to being
unable to figure out how to link tblProduction to
tblEmployeeProduction. Somehow, and I confess I have forgotten how, I
successfully linked tblProduction with tblProductionOperation and have
forms and queries that work well with that configuration. I need to
link all three tables I would think since they are completely related
to each other. In other words, I suspect that even though I have Date,
Dept, and Shift recorded in both tables that I won't be able to
relate, query, report data from the 3 main tables if
tblEmployeeProduction is not linked to tblProduction via the composite
key.

You don't need to set up a Relationship in order for you to link a main form
and subform together in a form, as you can do this through the
LinkChildFields and LinkMasterFields properties of a subform control. You
could use a subform for the tblEmployeeProduction data, and the main form
would let you choose the desired values for the ProductionDate, Dept, and
Shift fields. You then would put the names of the controls that are used to
select the ProductionDate, Dept, and Shift data in the LinkMasterFields
property (e.g., cboProductionDate; cboDept; cboShift) - note that you do
separate each control name with ; character. Then you'd put the three fields
in the LinkChildFields property (ProductionDate; Dept; Shift). This will
link things together, and whatever values you select in the main form will
automatically be entered in new records in the subform.

But linking those 3 fields from tblProduction(1) to
tblEmployeeProduction (m) results in this message: "No unique index
found for the referenced field of the primary table."

This will be seen if you try to drag just one field at a time from
tblProduction to tblEmployeeProduction. See above for how to do this with
multiple fields.

The primary key for tblEmployeeProduction is an autonumber field
[EmpProdID] and I have an index on ProductionDate, Department, and
Shift. I am attempting to enforce referential integrity.

Question: Do I have these tables setup appropriately to capture the
info I need and if not what should I do differently? Specifically, how
should I link or design tblProduction, tblEmployeeProduction, and
tblProductionOperation to best capture the data I need?

I guess I've gone on enough and I hope this is clearer and not more
confusing.

Thank you as always,

Tim