how many staff have 1 skill, how many staff have 2 skills, etc.

[Guest]

dear access lovers,

one table (2000 records), 2 columns, staff_id (700 unique records) and
skill_id (10 unique records). one staff can have one or more skill and I need
to know how many staff have 1 skill, how many staff have 2 skills, etc.

somehow it seems easy on paper but I can't found anything on the forum
because I don't know what to look for!!

regards from rome,
chris90

 

[Guest]

You could create a query that counts the Skills per person:

Select StaffID, Count(SkillID) from tablename Group By StaffID

Then perform other queries or reports based on it.

Steve Clark, Access MVP
fmsinc.com/consulting

 

[John Spencer]

Two query solution:
QueryOne: Save As StaffSkillCount - this will count the number of skills for
each staff member
SELECT StaffID, Count(Skill_ID) as CountOfSkills
FROM TheTable
GROUP BY StaffID

QueryTwo: Use QueryONE
SELECT CountOfSkills, Count(StaffID) as NumPeople
FROM StaffSkillCount
GROUP BY CountOfSkills

That can usually all be done in one query
SELECT CountOfSkills, Count(StaffID) as NumPeople
FROM
(SELECT StaffID, Count(Skill_ID) as CountOfSkills
FROM TheTable
GROUP BY StaffID) as BaseQuery