Skills database advice

[Little Elvis]

I have a new client. They are building a HR resource skills database. The
number of skills per person is about 1000. yes 1000. There are categories of
the skills,
so I can break up the 1000 into perhaps 20 tables of 50 columns each. One
column for each skill. This will make forms and subforms easy to build...but
I could also use a alternate table design where I store
PersonID, SkillID, SkillLevel
each "person" would get the full set of skills initialized from a central
table and SkillLevel would be NULL where the person has no experience.
This would be better I think, but the forms would need to be built with
continuous option. Which does not look too nice.
Is there another innovative way I could do this?

Thanks,

 

[NetworkTrade]

I believe you would have a SkillsList table that would be 2 columns the skill
name and the skill category (plus a column for the key in autonumber
mode)...this is a static list table that wound have 1000 records.

Then you would have your ClientTable.....you add their core contact info and
have a second table as the ClientSkills Table cross referenced by a common
key field. A client might have 1 skill or 100.

The CLientTable will be the source for the Main form.
You will add the ClientSkills table as a subForm to the main form.
Since you will have the cross referencing common field the subform wizard
will set this up when you add the subform.

The ClientSkillsTable links to the SkillsList so you don't have to re-enter
that info manually each time - if you are self taught the 'LookUp' feature in
the table's datatype is intended to get you that link cross reference set
up....

 

[John W. Vinson]

I have a new client. They are building a HR resource skills database. The
number of skills per person is about 1000. yes 1000. There are categories of
the skills,
so I can break up the 1000 into perhaps 20 tables of 50 columns each. One
column for each skill. This will make forms and subforms easy to build...but
I could also use a alternate table design where I store
PersonID, SkillID, SkillLevel
each "person" would get the full set of skills initialized from a central
table and SkillLevel would be NULL where the person has no experience.
This would be better I think, but the forms would need to be built with
continuous option. Which does not look too nice.
Is there another innovative way I could do this?

Well, not particularly *innovative* - the idea was around even before Codd and
Date's book published 40 years ago.

If an employee doesn't have a skill just *don't insert a record* in this
table. Add it when they evince mastery of the skill.

Nothing wrong with a continuous form (if it's properly designed), in my
opinion; certainly a LOT better than a form with 1000 checkboxes, and VASTLY
better than 20 tables with 50 fields each... OUCH!

 

[rpw]

If I understand the description of 1000 skills correctly, there are 20
different categories and each category has about 50 different skills. If
this is correct, then use NetworkTrades suggestion for table design and dump
the 20 table idea. And also heed Mr. Vinson's instructions.

Maybe some sample data will help you "see" the solution offered.

tblSkills
SkillID SkillCategory SkillDescription
1 Office Word
2 Office Excel
3 Office Access
4 AutoMaint Tune-up
5 AutoMaint OilChange

You can have your entire 1000 skills in this table, no limit on categories
(even though you currently count 20), no limit on skills per category, no
limit on skills.

You would have a people table for things relating only to the person.

tblPeople
PeopleID (PK)
FirstName
LastName
DOB
(etc.)

Then you would have a linking table for connecting people to skills and
skills to people:

tblPeopleSkills
PeopleID
SkillsID
DateSkillAcquired
(and maybe some other fields that might describe things about the
people/skill combination)


When you set up a "Many-To-One" form, the person would be the main form and
the skills would be the sub-form listing all of the different skill that
person might have. I can image that you could use 2 cascading combo boxes to
populate the continuous subform. The first would list the category and the
second would be populated with the skills within that category. (how to do
all this is a bunch of different subjects/postings but you should be able to
research the ideas in Access books or here)

A report then could be produced where you would have the persons name at the
top and all of the associated skills of that person listed underneath.

Hope this helps...