[SQL] 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.

Continue reading...