Training matrix - convert from excel

[Guest]

I am trying to convert a training matrix that I inherited from excel to
access. I have been searching the groups and trying different things with
limited success. I am in need of some tips and advice.

Basically, the spreadsheet is used to keep track of skills that our
department members have or identify where training is needed. Across the top
of the spreadsheet are the skills, down the left side is the employee name,
and the intersection of the two has a number from 1 - 5 that indicates
whether they have been trained, need training, have been trained and have
applied the skill, etc.

skill1 skill2 skill3
empl1 1 3 2
empl2 3 5 2
etc.
At the bottom of the spreadsheet there is a "legend" that tells what the
numbers in the matrix mean.

Here is what I have come up with for tables:
tblEmpl
emplID (PK)
LastName
FirstName
Location

tblSkillLevelIndex - this is the number in the matrix
skillLevelID (PK)
skillLevelDesc

tblSkills - this is a text description of the skill. Didn't see a need for
an index value.
skillID (PK)

tblMemberSkills
memberID (PK)(FK)
skillLevelID
skillID (PK)(FK)

All of the tables have a 1 to many relationship with tblMemberSkills.

I have created forms that will let me enter the information for each
individual. But, due to the amount of information in the excel spreadsheet, I
would really like to be able to enter information like I do in excel, or else
import it. I have had a lot of problems trying to import it, some due to the
way it has been entered in excel, and some due to my abilities.

So, I guess I have 2 questions..... 1) does my database design look OK for
what I want to do, and 2) can I create a form that would display the
information, and allow editing of the information, like I can in excel.
(Please, no flames about a database not being a spreadsheet. I understand
that.) I read a post that does something similar with an Excel ActiveX
object in a form, but had problems with the way that I would like to edit the
matrix (would like to just be able to type in a value of 1 - 5).

Thanks in advance for any/all help!!!!!

 

[tina]

1) does my database design look OK for

what I want to do

in tblMemberSkills, i'll assume that memberID, as a foreign key field, links
back to emplID in tblEmpl. in that case, the tables themselves look okay.
note that i probably *would* use an Autonumber (or an Number/Integer or
Number/Byte) for the primary key in tblSkills, rather than using the text
field that holds a description of the skill, which is presumably a minimum
of several words. using a numeric key will save space, for one thing. and
using the description as the PK means that if you ever change a
description - to clarify meaning, or just to fix a typo - you'll need to
cascade update every record that uses that description. i prefer PK values
that never change.

hth

 

[Guest]

in tblMemberSkills, i'll assume that memberID, as a foreign key field, links

back to emplID in tblEmpl.

Yes it does. Should have mentioned that.

note that i probably *would* use an Autonumber (or an Number/Integer or
Number/Byte) for the primary key in tblSkills, rather than using the text
field that holds a description of the skill, which is presumably a minimum
of several words. using a numeric key will save space, for one thing. and
using the description as the PK means that if you ever change a
description - to clarify meaning, or just to fix a typo - you'll need to
cascade update every record that uses that description. i prefer PK values
that never change.

I hadn't thought of all of that. Makes a lot of sense. I'll make that
change.

hth

Yes, it helps very much!!!! Thanks for your time!!!

 

[tina]

you're welcome!

Yes it does. Should have mentioned that.


I hadn't thought of all of that. Makes a lot of sense. I'll make that
change.


Yes, it helps very much!!!! Thanks for your time!!!

 

[Guest]

Does anyone have any thoughts on the 2nd part of my question? I did get an
answer to the first part, but REALLY need an answer to the rest!

Thanks for your time!!!