Indexes, Primary Keys, and Forms

[tbrogdon]

I have tried to answer this question on a different forum and also
have searched for several days through the groups for an answer...I am
stymied as to why this seems to be such a difficult issue for me to
describe and why it is not as simple to remedy as it may immediately
seem. Maybe my problem is in my initial design - I really don't know.
Anyway, here goes...

I have tblProduction and tblProductionOperation that are linked on
field ProductionID (pk in tblProduction). Also, tblProduction has
fields Date, Shift, and Dept. Unique combinations of those three
fields should be associated with a unique value in ProductionID - so
much so that it would make a lot more sense to input Date, Shift, and
Dept first and have Access check for duplicates before auto-generating
the autonumber for ProductionID because on the first day this db is
used "live" I should have the following entries in tblProductionand it
is possible currently to input conflicting data given my settings/
design:

ProductionID: 1
Date: 1/1/2008
Department: Hard Tool
Shift: 1
(end of main form fields that are entered in tblProduction - see
detailed description below)

ProductionID: 2
Date: 1/1/2008
Department: SoftTool
Shift: 1

ProductionID: 3
Date: 1/1/2008
Department: Hard Tool
Shift: 2

ProductionID: 4
Date: 1/1/2008
Department: Soft Tool
Shift: 2

ProductionID: 5
Date: 1/1/2008
Department: Hard Tool
Shift: 3

ProductionID: 6
Date: 1/1/2008
Department: Soft Tool
Shift: 3

That is what I want BUT I have created a Main form which is bound to
tblProduction (frmProduction - which has the four fields described
above in my example) and a form that is bound to
tblProductionOperation (frmProductionOperation) where data such as
PartNumber, QtyRun, OperatorName, etc. are entered.
frmProductionOperation is a continuous form embedded in frmProduction.

So again what I have is:
Main Form (frmProduction)
txtfield: txtProductionID (visible in form ONLY for troubleshooting
purposes) shows the value "autonumber."
txtfield: txtProductionDate (default = Date() )
cbo: cboDepartment
cbo: cboShift
--------------
Continuous form embedded in Main form (frmProductionOperation linked
to frmProduction by ProductionID)
cbo: Workstation
cbo: PartNumber
cbo: Operator
txtfield: QtyRun

When I open frmProduction the first time txtProductionID shows the
value "autonumber."
txtProductionDate value shows today's date. I hit <enter> and the
focus leaves txtProductionDate WITHOUT changing the value of
txtProductionID from "autonumber."

On the first keystroke entered in cboDepartment, the value of
txtProductionID increments to 1.

THEN I enter the remainder of the text for cboDepartment or select it
from the combo box. I hit <enter> again enter the shift number or
select it from the combo box.

NOTICE when the ProductionID was generated and REMEMBER that all
combinations of Date, Dept, and Shift MUST be unique and no
combination can share a ProductionID. And here is my problem...

With the example just described above it is possible for the user to
enter all of that data, go into the subform and enter data which is
immediately associated with the generated ProductionID, and then
WITHOUT leaving the form CHANGE either Shift or Dept in the Main form
and return to the subform WITHOUT an error and continue entering data
and now I have mixed data all associated with ONE auto-generated
ProductionID.

How can I insure that any unique combination of Date, Dept., and Shift
will ONLY be associated with one ProductionID? How can I have Access
check the values of Date, Dept, and Shift for duplicates in
tblProduction REGARDLESS of ProductionID even though it is the PK and
if Access does find a duplicate combination of those 3 fields it
instructs the user to review the data entered and make any necessary
changes BEFORE allowing the user to enter any data in the subform -
because it is currently possible to have duplicate values for Date,
Shift, and Dept associated with two different PKs and it is also
possible for the user to change the value of Dept or Shift after
entering detail data in the subform and the ProductionID doesn't
change and no error occurs.

Several people have attempted to give me sage advice on this and I
deeply apologize if I haven't been able to communicate the issue
properly. If I am overlooking something fundamental please help me to
see it. I am not imagining this problem and it is beginning to really
be a source of frustration.

Thank you very much in advance,

Tim

 

[Rod Plastow]

Hi Tim,

A confession: I only read your first two paragraphs in any detail. Have you
considered defining a compound index on your table consisting of the three
values and then assigning the unique property to this index?

Rod

 

[tbrogdon]

Have you

considered defining a compound index on your table consisting of the three
values and then assigning the unique property to this index?

Hi Rod,

Thanks for the honesty. Yes, in fact I have created a mutliple field
index by opening the table in Design View, clicking the Index button,
adding an index name in the far left column, and then added the three
fields in quiestion in the middle column of the grid. Then I went to
the lower left corner of the window and checked "yes" next to Unique.

This did work for awhile...but then for some reason it doesn't work
anymore. The Help file says to leave the Index window open after
adding a mulriple field index. Is this necessary for the index to work
correctly? If so, all I need to do is open the table in design view
again, open the index, leave it open, and close the table. Does that
sound right? So the index window just always needs to be open?

Thanks for the feedback and help!

Tim

 

[Ken Sheridan]

I have tried to answer this question on a different forum and also
have searched for several days through the groups for an answer...I am
stymied as to why this seems to be such a difficult issue for me to
describe and why it is not as simple to remedy as it may immediately
seem. Maybe my problem is in my initial design - I really don't know.
Anyway, here goes...

I have tblProduction and tblProductionOperation that are linked on
field ProductionID (pk in tblProduction). Also, tblProduction has
fields Date, Shift, and Dept. Unique combinations of those three
fields should be associated with a unique value in ProductionID - so
much so that it would make a lot more sense to input Date, Shift, and
Dept first and have Access check for duplicates before auto-generating
the autonumber for ProductionID because on the first day this db is
used "live" I should have the following entries in tblProductionand it
is possible currently to input conflicting data given my settings/
design:

ProductionID: 1
Date: 1/1/2008
Department: Hard Tool
Shift: 1
(end of main form fields that are entered in tblProduction - see
detailed description below)

ProductionID: 2
Date: 1/1/2008
Department: SoftTool
Shift: 1

ProductionID: 3
Date: 1/1/2008
Department: Hard Tool
Shift: 2

ProductionID: 4
Date: 1/1/2008
Department: Soft Tool
Shift: 2

ProductionID: 5
Date: 1/1/2008
Department: Hard Tool
Shift: 3

ProductionID: 6
Date: 1/1/2008
Department: Soft Tool
Shift: 3

That is what I want BUT I have created a Main form which is bound to
tblProduction (frmProduction - which has the four fields described
above in my example) and a form that is bound to
tblProductionOperation (frmProductionOperation) where data such as
PartNumber, QtyRun, OperatorName, etc. are entered.
frmProductionOperation is a continuous form embedded in frmProduction.

So again what I have is:
Main Form (frmProduction)
txtfield: txtProductionID (visible in form ONLY for troubleshooting
purposes) shows the value "autonumber."
txtfield: txtProductionDate (default = Date() )
cbo: cboDepartment
cbo: cboShift
--------------
Continuous form embedded in Main form (frmProductionOperation linked
to frmProduction by ProductionID)
cbo: Workstation
cbo: PartNumber
cbo: Operator
txtfield: QtyRun

When I open frmProduction the first time txtProductionID shows the
value "autonumber."
txtProductionDate value shows today's date. I hit <enter> and the
focus leaves txtProductionDate WITHOUT changing the value of
txtProductionID from "autonumber."

On the first keystroke entered in cboDepartment, the value of
txtProductionID increments to 1.

THEN I enter the remainder of the text for cboDepartment or select it
from the combo box. I hit <enter> again enter the shift number or
select it from the combo box.

NOTICE when the ProductionID was generated and REMEMBER that all
combinations of Date, Dept, and Shift MUST be unique and no
combination can share a ProductionID. And here is my problem...

With the example just described above it is possible for the user to
enter all of that data, go into the subform and enter data which is
immediately associated with the generated ProductionID, and then
WITHOUT leaving the form CHANGE either Shift or Dept in the Main form
and return to the subform WITHOUT an error and continue entering data
and now I have mixed data all associated with ONE auto-generated
ProductionID.

How can I insure that any unique combination of Date, Dept., and Shift
will ONLY be associated with one ProductionID? How can I have Access
check the values of Date, Dept, and Shift for duplicates in
tblProduction REGARDLESS of ProductionID even though it is the PK and
if Access does find a duplicate combination of those 3 fields it
instructs the user to review the data entered and make any necessary
changes BEFORE allowing the user to enter any data in the subform -
because it is currently possible to have duplicate values for Date,
Shift, and Dept associated with two different PKs and it is also
possible for the user to change the value of Dept or Shift after
entering detail data in the subform and the ProductionID doesn't
change and no error occurs.

Several people have attempted to give me sage advice on this and I
deeply apologize if I haven't been able to communicate the issue
properly. If I am overlooking something fundamental please help me to
see it. I am not imagining this problem and it is beginning to really
be a source of frustration.

Thank you very much in advance,

Tim

 

[Ken Sheridan]

Tim:

Looks like I hit the 'post' key too soon! This is what i should have sent:

To overcome both problems, i.e. that of duplicate Date, Shift, and Dept
values and that of the user changing the data in the parent form after
entering data in the subform you’d need to use Date, Shift, and Dept as the
composite primary key of tblProduction, and as the corresponding composite
foreign key in tblProductionOperation. This is perfectly valid as the three
column constitute a candidate key of tblProduction. You would not need the
ProductionID surrogate key columns at all then. As you have found, with your
present arrangement its easy for inconsistent data to be entered as the
ProductionID is independent of the values of Date, Shift, and Dept, and even
if you index these uniquely they can still be changed without any error
provided that the combination of values in each is not already represented in
another row in the table.

The subform and parent form would be linked on the three columns, which you
do by entering all three as the LinkMasterFields and LinkChildFields
properties of the subform control, separating them with semi-colons, i.e.
Date;Shift;Dept. BTW I’d recommend that you avoid using Date as a column
name as it could be confused with the VBA Date function. Something like
ProductionDate would be better.

You don’t need to show the Date, Shift, and Dept values in the subform as
these are automatically inserted into the underlying table via the link.

Provided that referential integrity is enforced, if a user attempts to
change the data in the parent form after entering data in the subform this
will raise a data error.

Incidentally the reason that the autonumber value is not generated until you
begin to enter the department data is that the DefaultValue property does not
initiate a new record; that only happens when you add data yourself. This is
usually advantageous in fact as it allows a user to back out of a new record
without having to undo it.

Ken Sheridan
Stafford, England

 

[tbrogdon]

Hi Ken and thank you for a thorough explanation of what I have
happening with my design. Thank you for recognizing that I truly want
to learn how to do this. I have to wonder if I have not received this
info before because of desire to avoid composite keys? I undersand
they may be problematic which had been leading me to believe that - in
my naivete - I must be mis-understanding why these had not been
brought to my attention before.

I do have a couple of questions - of course!

I completely understand making the ProductionDate (which is actually
what I already call it - thanks for the advice), Dept, and Shift
fields the pk in tblProduction. Then in tblProductionOperation are you
saying I should insert the same three fields so they can act as fk?
Upon re-reading I see that you are...so then I have to ask...since
ProductionDate, Dept, and Shift are the only fields in tblProduction,
what is the utility of tblProduction? Is it necessary to have this
table? Could all data be stored in tblProductionoperation? I believe
the answer is "No" as even though all the data on tblProduction is
repeated in tblProductionOperation that it still violates the rules of
normalization as the 3 fields in tblProduction act as a "single" unit.
Is that correct?

So my next question then is in the future how do I refer to this
composite key of necessary?

Thank you again for you time,

Tim

 

[Ken Sheridan]

Tim:

You are quite right; you need the tllProduction table to ensure consistency
of the data. By having each valid combination of the three values stored
only once in that table (the ‘referenced’ table in the jargon), you can’t
have any invalid entries made in the tblProductionOperation table (the
‘referencing’ table). This could be just a simple typo, or a slightly
different way users might insert the ‘same’ value. I once worked on a
database where I found three versions of myself as author in a table of
technical papers, K Sheridan and K W Sheridan, both correct but different,
and K V Sheridan, clearly a typo. If there had been an Authors table with me
just once this could not have happened. The fact that the key is made up of
three columns doesn’t make any difference; the principle is exactly the same.

You don’t have to enter the three values manually in
tblProductionOperation’s subform of course. By linking it to the parent form
on the three columns the values are automatically inserted when a new record
is entered in the subform.

I’m not too surprised that no-one has suggested this solution before. There
is a natural inclination to use single column surrogate keys, usually an
autonumber, rather than multi-column keys, which is fine most of the time,
but as you’ve found this can sometimes leave a loop-hole through which bad
data can slip in if the user doesn’t fully understand the implications of
what might seem to them to be quite innocuous actions. By adopting a more
classically relational approach this is avoided n this instance.

I’m not quite sure what you are asking with your second question “So my next
question then is in the future how do I refer to this composite key of
necessary?â€.

Ken Sheridan
Stafford, England

 

[tbrogdon]

Hi Ken.

I really can't thank you enough for detail. I have been wondering if I
am absolutely missing something withthis issue...but enough of that.

What I was referring to with my next question was this:

Along with tblProduction, I have tblEmployeeHours which was linked to
tblProduction by tblProduction,ProductionID. This table is used to
collect the actual amount of hours worked by a given employee on a
daily basis because any employee can work either more or less than 8
hours on any given day.

Ultimately, we are going to use this database to report and track
efficiency and productivity by any combination of criteria (Date,
Shift, Dept, daily, weekly, etc.) so we need to input each employees
actual working hours on a shift basis. It is OK if an employee works
through an entire shift and into the next. All that is critical is the
total amount of hours.

My question - directly - is will I need to add ProductionDate, Dept,
and Shift fields to tblEmployeeHours and any other table that is a
child link to tblProduction since I am doing away with ProductionID OR
is there a better way to do this?

Thank you again. This is really helping me.

Tim

 

[Ken Sheridan]

Tim:

You should add ProductionDate, Dept and Shift columns to the
tblEmployeeHours table. Theoretically you could keep the ProductionID column
in tblProduction as well as having the three-column key, but it would leave
the possibility of the same problem being encountered with tblEmployeeHours
as was the case with tblProductionOperation.

Using a 'natural' key does have another advantage in that you can often
avoid having to include the referenced table in a query, which would
otherwise not be the case if a surrogate key is used. Another area in which
they are better is when using correlated combo boxes on continuous forms.
With surrogate keys you have to resort to a hybrid control for this,
overlaying a text box on a combo box, whereas with a natural key a single
combo box control can be used.

Sometimes a surrogate key is not really practicable however, e.g. with an
Employees table, as names are not unique; I once worked in the same office as
two Maggie Taylors! While they had different job titles, so it might be
thought that a composite key could have been used, but its still
theoretically possible that two employees could have the same name and job
title, so its not a valid option. My doctor's practice uses Last Name,
Gender and Date of Birth as the key for its patients, but even that's not
good enough; I was at a hospital clinic once when two women of the same name
and same date of birth were attending on the same day.

Ken Sheridan
Stafford, England

 

[tbrogdon]

My doctor's practice uses Last Name,

Gender and Date of Birth as the key for its patients, but even that's not
good enough; I was at a hospital clinic once when two women of the same name
and same date of birth were attending on the same day.

It's much as the thought that "Nature will find a way..." There will
always be an exception.

Ken...you have given me a lot of good information. Thank you very
much. I've begun implementing it and it does make sense to me now.

Thank you,

Tim