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

C

ch90

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
 
S

S.Clark [Access MVP]

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
 
J

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
 
B

Bob Miller

Add a dummy text field to your table. Then create this query:
TRANSFORM Count(Table.skill_id) AS CountOfskill_id
SELECT Table.staff_id
FROM Table
GROUP BY Table.staff_id
PIVOT Table.dummy;
 
Top