If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Skills database advice
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, |
#2
|
|||
|
|||
Skills database advice
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.... -- NTC "Little Elvis" wrote: 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, |
#3
|
|||
|
|||
Skills database advice
On Thu, 6 Mar 2008 06:19:08 -0800, Little Elvis
wrote: 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! -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Skills database advice
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... -- rpw "Little Elvis" wrote: 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, |
Thread Tools | |
Display Modes | |
|
|