Match Suitable Records 1

[KC744]

I have this database that requires me to find suitable workers for certain jobs.

Each staff members has 5 different skills, with the skill level either Low, Average or High which are in a combo box. This is in a form which holds staff data. In another form are the skills and skills levels required for certain jobs also in a combo box each low, average or high.

What I need to do is find which employees have suitable skills levels to do that particular job. Considing if the job requires one of the skills to have an average rating, workers which have an average or high rating are suitable for the job.

If you have a solution for this problem of have any tips for me to get around this please tell me. This is for an important assignment for my course at university. Thanx

 

[link9]

Sounds like you need a SQL Statement, but without table names or knowing what data resides in where, it's hard to give a be-all-end-all solution.

Basically, if you aren't working with multiple tables, the syntax of the query that you want to run for finding an employee that had all five skill levels at 'Low', and I'll assume that that is the actual string value in the table that you will be searching for, then your query would look like:

SELECT employeeName FROM skillTable WHERE skill1 = 'low' and skill2 = 'low' and skill3 = 'low' and skill4 = 'low' and skill5 = 'low';

That would return to you one column full of the names of the employees that meet that criteria.

On the other hand, if you are storing the values 0, 1, and 2, that would represent those skill levels numerically (a much better way to store the data), then you could search just like you did above, except your expressions would be:

skillN >= 1

Additionally, you may choose to combinations of and's and or's to get your data instead of just the and's like I posted. It just all depends.

let me know if that helps... if not, post back in some greater detail.

Paul Prewett

 

[KC744]

Thanx for your reply, i've tried the statement you gave me but i can't get it to work. So i have included a sample of what the tables look like the the field names included.

(Name of table is "Jobs List&quot
Name Data Type Field Size

Job Title(Primary Key) Text 4
Word-Processing Text,Combo Box(Low, Average, High)
Typing Text,Combo Box(Low, Average, High)
Filing Text,Combo Box(Low, Average, High)
Shorthand Text,Combo Box(Low, Average, High)
Book-Keeping Text,Combo Box(Low, Average, High)

(Name of table is "Personnel&quot

Emp No(Primary Key) Text 4
Word-Processing Text,Combo Box(Low, Average, High)
Typing Text,Combo Box(Low, Average, High)
Filing Text,Combo Box(Low, Average, High)
Shorthand Text,Combo Box(Low, Average, High)
Book-Keeping Text,Combo Box(Low, Average, High)

What I need to do is find all the employees' that have a skill level that is equal or above to the corresponding fields in Jobs List.

Thanks for all of your help in this I really appreciate it. If you know how to solve this, or know of a better way to do this please let me know. Cheers!

 

[link9]

Ok, let's say that you need a secretary --

For this position, you need a person with the following competencies:
Word-Processing High
Typing High
Filing Average
Shorthand Average
Book-Keeping Low

Now, when you say that the data type is combo box -- I'm not sure I understand that. I'm not an Access guru or anything, but I do know that the actual data type is Text from what you have posted up there (combo box is a control, not a data type)...

Ok, so go into the query builder, view the SQL pane, and paste the following code into it:

SELECT Personnel.[Emp No] FROM Personnel WHERE Personnel.[Word-Processing] = 'High' AND Personnel.[Typing] = 'High' AND Personnel.[Filing] = 'Average' AND Personnel.[Shorthand] = 'Average' AND Personnel.[Book-Keeping] = 'Low';

That will pull the desired data from the table, Personnel, if and only if your data types are, in fact, text, and the values are exactly as you stated above (High, Average, Low -- one of the three choices)...

Let me know how it works out
Paul Prewett