Originally Posted by
Chevy757IT
The only question I have is how would I get the percentage they are qualified. If they completed 25 of the 53 tasks I would like it to say 47% and then be able to view a report that displays all the users and what percentage they are at completing the qualification?
When all users must have all qualifications, then you need a query like
Code:
SELECT u.UserID, COUNT(uq.QualificationID)/(SELECT COUNT(QualificationID) FROM tblQualifications) AS Percentage
FROM tblUsers u LEFT JOIN tblUserQualifications uq ON uq.UserID = u.UserID
GROUP BY u.UserID
When the list of required qualifications varies for users, the you need an additional table (e.g. tblRequiredQualifications: RequiredQualificationID, UserID, QualificationID) where you register qualifications required for every user. On fly, the query will be something like
Code:
SELECT u.UserID, COUNT(uq.QualificationID)/(SELECT COUNT(rq0.QualificationID) FROM tblRequiredQualifications rq0 WHERE rq0.UserID = u.UserID) AS Percentage
FROM (tblUsers u LEFT JOIN tblUserQualifications ON uq.UserID = u.UserID) LEFT JOIN uqtblRequiredQualifications rq ON rq.UserID = uq.UserID AND rq.QualificationID = uq.QualificationID
WHERE rq.QualificationID Is Not Null
GROUP BY u.UserID