[SOLVED] Create a MS Access Pivot Query that returns rows even if there are null values

Create a MS Access Pivot Query that returns rows even if there are null values

I'm trying to create a pivot table in MS Access based on an Employee List table, a Skills list table, and a table that contains data on what employee has what level of training for each skill. The table should return default values if there is no row found in the still level table.

Employee Table
ID EmployeeName
1  David
2  Bob
3  Steven
4  Richard

Skills List Table
ID SkillName
1  Skill 1
2  Skill 2
3  Skill 3
4  Skill 5

SkillsTable
ID  EmployeeID  SkillID  SkillLevel
1   1           1        3
2   3           4        2

What I need for a final result looks like this. If there's no record found, then 1 is the default skill level.

           Skill 1    Skill 2   Skill 3   Skill 4   Skill 5
David        3           1         1        1         1
Bob          1           1         1        1         1  
Steven       1           1         1        2         1
Richard      1           1         1        1         1

Basically, even though there's no row in the Skills Table, I should still get all employees and all skill names returned.

Hopefully that makes sense. If anyone has any suggestions, I'd appreciate the help. thanks

I created an SQL that gives me a pivot, but it's only returning data if there are rows found in the Skills table, I'm not getting all the employee names or all of the skill names.

Solution

The following SQL will give you the result you were looking for. Note that I removed the space from the Skills List table name so that it is now called SkillsList:

TRANSFORM First(IIf(Nz([SkillsTable].[SkillLevel])<>"",[SkillsTable].[SkillLevel],1)) AS SkillLvl
SELECT qryEmpSkill.EmployeeName
FROM (SELECT Employee.EmployeeName, Employee.ID, SkillsList.ID, SkillsList.SkillName FROM Employee, SkillsList)  AS qryEmpSkill 
LEFT JOIN SkillsTable ON (qryEmpSkill.SkillsList.ID = SkillsTable.SkillID) AND (qryEmpSkill.Employee.ID = SkillsTable.EmployeeID)
GROUP BY qryEmpSkill.EmployeeName
ORDER BY qryEmpSkill.EmployeeName
PIVOT qryEmpSkill.SkillName;