Newbie Normalization Question - Ref: Table design

[M Davidson]

Hello All,

First, I'd like to say I've learned a lot from just lurking in this
forum. (After reading this question, you might not think so... but I have.)
A heartfelt "Thanks" to the knowledgeable few that take the time to lend
assistance to less knowledgeable strangers. Your time and assistance is
greatly appreciated.

This is probably a really stupid question... it stems from my struggle
with fully understanding normalization. But through numerous failed
attempts, many hours of struggling though what turned out to be simple
tasks, and the guidance of this forum, I've come to appreciate it's
importance. I'm trying to avoid emulating a spreadsheet.

Currently I have the skeleton for a Trainee evaluation database. The
Trainees are graded daily in 30 separate categories. The grade is a numeric
value. During different phases of the training period, the trainee will
have different instructors. All 30 categories have to be assigned a numeric
value for each day of observation. If a category is not observed, then a
value of 0 *must* be entered.
-------------
I have separate tables for the following:

tblTraineeInfo - PK Employee ID Number
tblInstructorInfo- PK Employee ID Number
tblDailyEvaluation---This is where the scores are logged at this time
tblSuperSched --- Supervisor's planned training schedule for each Trainee
------------------

The tblTraineeInfo and tblInstructorInfo are related one to many to
tbleDailyEvalution via Employee numbers. (they are automatically unique)
The tblDailyEvaluation is autonumbered for Primary Key. I'm (attempting) to
use a "find unmatched query" comparing tblSuperSched to tblDailyEvaluation
to allow the supervisor to find Daily Evaluations for trainees that have not
been completed.

***Question*** My concern is that I have 30 fields in each Daily evaluation
(tbleDailyEvaluation) that correspond to the 30 categories (in addition to
other identifying and necessary information, i.e.. shift, specific
assignment, etc...). Should I create two tables, one called
tblDailyEvaluations and another for the categories that holds the scores
(tblScores). With tblDailyEvaluations housing the extraneous identifying
information for the training day and tblScores just housing the scores by
category?

I think the way I have it set up now is proper, but I've read (here) that
"records are cheap, fields are expensive" What would be the most efficient
way to house the scores and relate them to the training day without having a
table with 30+ columns. Like I said, I may be way off base and have been
staring at the screen too long... but 30+ columns seems inefficient... it
even looks like a spreadsheet. **A key point, I can't let the end user
select the category in the scoring process, they have to be forced to enter
a score for each of the 30 categories from 0-7.** This is what's causing
me the greatest concern. If I create a table that has a lookup field for
category and a field for score, I don't see how I'll be able to force an
entry for each category. I also have this sinking feeling that it could be
easily solved through some programatic looping process that cycles through
each scoring category... But I'm still very confused by VB. A
non-programatic solution would probably keep me from breaking out in
hives...<grin>. I've only cut and pasted some very basic code. I have
bought some reference material on VB though, and I am trying to learn.

Thanks for any help, and I apologize for being so verbose... it's a
personality flaw and it get's worse with age. In addition, I thought about
posting this in the "tables" forum, but considering the basic nature of the
question, I thought it best to stay in the shallow end of the pool.

Mike D.

 

[John Nurick]

Hi Mike,

Comments inline.

Hello All,

First, I'd like to say I've learned a lot from just lurking in this
forum. (After reading this question, you might not think so... but I have.)
A heartfelt "Thanks" to the knowledgeable few that take the time to lend
assistance to less knowledgeable strangers. Your time and assistance is
greatly appreciated.

This is probably a really stupid question... it stems from my struggle
with fully understanding normalization. But through numerous failed
attempts, many hours of struggling though what turned out to be simple
tasks, and the guidance of this forum, I've come to appreciate it's
importance. I'm trying to avoid emulating a spreadsheet.

Currently I have the skeleton for a Trainee evaluation database. The
Trainees are graded daily in 30 separate categories. The grade is a numeric
value. During different phases of the training period, the trainee will
have different instructors. All 30 categories have to be assigned a numeric
value for each day of observation. If a category is not observed, then a
value of 0 *must* be entered.
-------------
I have separate tables for the following:

tblTraineeInfo - PK Employee ID Number
tblInstructorInfo- PK Employee ID Number
tblDailyEvaluation---This is where the scores are logged at this time
tblSuperSched --- Supervisor's planned training schedule for each Trainee
------------------

The tblTraineeInfo and tblInstructorInfo are related one to many to
tbleDailyEvalution via Employee numbers. (they are automatically unique)
The tblDailyEvaluation is autonumbered for Primary Key. I'm (attempting) to
use a "find unmatched query" comparing tblSuperSched to tblDailyEvaluation
to allow the supervisor to find Daily Evaluations for trainees that have not
been completed.

***Question*** My concern is that I have 30 fields in each Daily evaluation
(tbleDailyEvaluation) that correspond to the 30 categories (in addition to
other identifying and necessary information, i.e.. shift, specific
assignment, etc...). Should I create two tables, one called
tblDailyEvaluations and another for the categories that holds the scores
(tblScores). With tblDailyEvaluations housing the extraneous identifying
information for the training day and tblScores just housing the scores by
category?

I think the way I have it set up now is proper, but I've read (here) that
"records are cheap, fields are expensive" What would be the most efficient
way to house the scores and relate them to the training day without having a
table with 30+ columns. Like I said, I may be way off base and have been
staring at the screen too long... but 30+ columns seems inefficient... it
even looks like a spreadsheet.

Another point is that one day the power that be will change the
categories, and you'd have to restructure the entire table. I'd have one
table called something like
tblEvaluations
EvaluationID (PK)
Title
Description
Required (boolean: true if this evaluation must be done
every day, false if it's optional - to allow future
rule changes)
MinScore (for data validation)
MaxScore

and another
tblTraineeEvaluations
EmployeeID )
EvaluationID ) 3-field primary key
EvaluationDate )
Score
Comment (maybe)

So a full day's evaluation of one trainee results in 30 records in
tblTraineeEvaluations. You can check that there are 30 by using
something like this

If DCount("EvaluationID", "tblTraineeEvaluations", _
"EmployeeID = " & lngEmployeeID & _
" AND EvaluationDate = #" & Format(Date(), "mm/dd/yyyy") _
& "#") <> 30 Then
MsgBox "Incomplete!"
End If

or you can identify the missing ones with a query along these lines:

SELECT EvaluationID FROM tblEvaluations WHERE EvaluationID NOT IN
(SELECT EvaluationID FROM tblTraineeEvaluations WHERE TraineeID = 123
AND EvaluationDate = #03/31/2004");

I'd set up a form bound to tblTraineeInfo with a subform bound to a
query on tblTraineeEvaluations WHERE EvaluationDate = today, and use
code behind the form to count the number of evaluations entered before
allowing the user to close the form or move to another Trainee.

Probably I'd just warn the user about the missing items at this stage
(on the assumption that sometimes there'll be a genuine reason that
they're not all available). But I'd also set up a report of all missing
data so someone could get a list of the gaps and go and track it down.

 

[M Davidson]

John,

Thanks so much for your reply.
I believe I understand your advice, but I'd like to ask three questions
to be doubly sure.

1) I've never used a "multi-field key" (is that proper terminology?)
After examination, I think I understand it's purpose and method. Please
advise if this is correct: Normally Access requires a key to be unique.
When Access comes across a "multi-field key" it seems, by your example, that
Access still requires a unique key, but in this instance it somehow takes
into consideration, some sort of combination of the three fields AND as long
as the combination of the three fields is unique to a given record, the need
for a unique key is met. The reason for doing this is that it allows a
single field that is in the "multi-field key" to repeat... as long as the
combination is unique. It appears that the benefit to this is that I will
automatically have, in this case, 2 levels of grouping already assigned to
the records, (date and category). If that's so, it makes the process of
looking at trends in scores by category much simpler at a later time...
Graphing would be easier also, I think... everything is already broken down
by date. Much tidier... smaller packages.

Is the above correct?

2) To which event would I assign the code that you listed. Would the
form's BeforeUpdate event be proper?

3) On the form/subform in which data would be entered, Is there a way that
I could have all 30 categories already listed in the subform when the form
is opened? Or can I have a tabbed subform (I don't know if that's even
possible) with each tab predesignated to a category or group of categories.
Or is it possible for me to create 30 combo boxes and set the default value
to be shown as different category? I don't think I can do those things
because each "score" will be a seperate record in the tblEvaluations. What
would you do to make it easier and less frustrating for the user. I'm
afraid if I just use a combo box in the subform, it would be very easy for a
user to accidentaly skip a category.

Thanks again for the guidance you've already given, deeply appreciated. Any
additional help would be wonderful.

Mike D.

 

[John Nurick]

John,

Thanks so much for your reply.
I believe I understand your advice, but I'd like to ask three questions
to be doubly sure.

1) I've never used a "multi-field key" (is that proper terminology?)
After examination, I think I understand it's purpose and method. Please
advise if this is correct: Normally Access requires a key to be unique.
When Access comes across a "multi-field key" it seems, by your example, that
Access still requires a unique key, but in this instance it somehow takes
into consideration, some sort of combination of the three fields AND as long
as the combination of the three fields is unique to a given record, the need
for a unique key is met. The reason for doing this is that it allows a
single field that is in the "multi-field key" to repeat... as long as the
combination is unique. It appears that the benefit to this is that I will
automatically have, in this case, 2 levels of grouping already assigned to
the records, (date and category). If that's so, it makes the process of
looking at trends in scores by category much simpler at a later time...
Graphing would be easier also, I think... everything is already broken down
by date. Much tidier... smaller packages.

Is the above correct?

Pretty much. It's a multi-field unique index, which means that the
database engine won't allow two records to have the same combination of
values in those fields. It's also the primary key, which (speaking
loosely) means that any record in the table can be uniquely found by the
key value. And yes, it means you can have multiple records for each
Trainee, and for each Trainee and each Evaluation, but only one record
for that trainee and that evaluation on any particular day.

2) To which event would I assign the code that you listed. Would the
form's BeforeUpdate event be proper?

It depends on how you want things to work. If whenever anyone enters
data there will always be all 30 values available and time to enter them
without being interrupted, then the Form_BeforeUpdate() event would
probably be right: don't allow them to close the form or move to another
trainee before they've completed the data entry for the present one.

In the real world it seems likely that there will be gaps and
interruptions. In that case, I'd probably put code in the BeforeUpdate
event to warn the user if any evaluations are missing.

There'd also have to be a query to scan the entire table and list all
the missing evaluations so they can easily be added later.

3) On the form/subform in which data would be entered, Is there a way that
I could have all 30 categories already listed in the subform when the form
is opened? Or can I have a tabbed subform (I don't know if that's even
possible) with each tab predesignated to a category or group of categories.
Or is it possible for me to create 30 combo boxes and set the default value
to be shown as different category? I don't think I can do those things
because each "score" will be a seperate record in the tblEvaluations. What
would you do to make it easier and less frustrating for the user. I'm
afraid if I just use a combo box in the subform, it would be very easy for a
user to accidentaly skip a category.

Thanks again for the guidance you've already given, deeply appreciated. Any
additional help would be wonderful.

Mike D.

 

[John Nurick]

3) On the form/subform in which data would be entered, Is there a way that
I could have all 30 categories already listed in the subform when the form
is opened? Or can I have a tabbed subform (I don't know if that's even
possible) with each tab predesignated to a category or group of categories.
Or is it possible for me to create 30 combo boxes and set the default value
to be shown as different category? I don't think I can do those things
because each "score" will be a seperate record in the tblEvaluations. What
would you do to make it easier and less frustrating for the user. I'm
afraid if I just use a combo box in the subform, it would be very easy for a
user to accidentaly skip a category.

All these are possible but need fairly advanced VBA and SQL skills. IMHO
it's much simpler to use a continuous subform with a system to inform
the user if any evaluations have been missed and which they are.

 

[M Davidson]

John,

Thank you so much for your assistance, I'm going to roll up my sleeves
and see if I can get this thing to work.

Mike D.

 

[Duane Hookom]

There are a couple sample databases at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that
might provide some ideas. Check out both the At Your Survey and Employee
Evaluation.

--
Duane Hookom
MS Access MVP

John,

Thank you so much for your assistance, I'm going to roll up my sleeves
and see if I can get this thing to work.

Mike D.