Apr 21 2012, 12:59 PM
Hi I am looking for some help for my son for a project I have given him.
He is trying to use Access to give me a way of searching for employees at my workplace with particular skills.
So he has a table of employees. I have given him a list of skills (e.g. Excel, Word, customer service trained ... ). He wants to be able to create a query for me so I can search for employees with let's say a couple of those skills that I select. What is the best way to go about it? He thought about having another table with each skill being a field, and then have a form where employees can tick the level of each skill they have) but he is not sure that he could make a query work based on that structure. Is there standard way to do this?
Thanks for your time in considering this post.
Apr 21 2012, 01:12 PM
Welcome to UtterAccess.
Yes, it is wise to design your tables before thinking about forms, etc. And I agree with your son that "tick boxes" are probably not a good way to go. They "look" simple on a form, but making them work in a complex query takes a lot of additional code that you should avoid if possible.
Start by searching for templates that might be similar to your needs. Employee skills has to be something that a lot of places track.
One caveat, there will probably be a lot of variations, so don't get stuck with the first one you find.
Also here is a list of good readings to help you get started.
Apr 21 2012, 04:15 PM
This is a classic Many to Many relationship: each Employee will have zero (!), one, or (hopefully) many Skills; each Skill will be possessed by zero (!!), one or many Employees.
The proper structure for this has THREE tables:
EmployeeID <primary key>
<other biographical and personal data>
SkillID <primary key>
EmployeeSkillID <autonumber primary key>
EmployeeID <link to Employees>
SkillID <link to Skills>
<other fields about this employee with respect to this skill, e.g. skill level, certification, etc.>
You could have a continuous Subform on a form based on the Employees table, with the EmployeeID as the master/child link field and the SkillID bound to a combo box, so you could select that Joe is good at carpentry, cement work, and painting, and Jane is expert with front-end loaders and heavy trucks.