Access SQL crosstab with multiple outer joins

mickferrari
mickferrari used Ask the Experts™
on
Hello,

I'm trying to create a crosstab query that will show all courses (Column Heading), all staff (row Heading) and status (Value). I have attached a screen shot of my query in design view and the SQL is as below:

TRANSFORM Last(Qry_GA_Status.status) AS LastOfstatus
SELECT Employees.[Employee ID]
FROM [Employee Training Course Details] LEFT JOIN (Employees LEFT JOIN Qry_GA_Status ON Employees.[Employee ID] = Qry_GA_Status.[Employee ID]) ON [Employee Training Course Details].[Course number] = Qry_GA_Status.[Course Number]
GROUP BY Employees.[Employee ID]
PIVOT [Employee Training Course Details].[Course Details];

The problem I'm having is that I cant have the joins I've created by using the query builder and my SQL is crap. What I want to achieve is a complete list of course names (Course details) as the Column Headings, a complete list of Employees as the Row Headings and using the relationship to include a Status of the course as the Value. The Status just displays if they have passed the course or not.

So far all I've been able to accomplish is either a complete list of employees that don't show me all the courses because there isn't a related "Status" record. and If i go the other way all i get is a complete list of courses and only the employees that have a related record to those course which wont include employees that have not done a course.

Any help or suggestions would be appreciated.
Untitled.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2013

Commented:
Try this:
TRANSFORM Last(Qry_GA_Status.status) AS LastOfstatus
SELECT Employees.[Employee ID]
FROM Employees, [Employee Training Course Details] LEFT JOIN Qry_GA_Status ON Employees.[Employee ID] = Qry_GA_Status.[Employee ID] AND [Employee Training Course Details].[Course number] = Qry_GA_Status.[Course Number]
GROUP BY Employees.[Employee ID]
PIVOT [Employee Training Course Details].[Course Details];

Open in new window

The idea is that you use a CROSS JOIN on Employees and [Employee Training Course Details] tables (there is no CROSS JOIN in Access, so I am just using a comma-list of these two tables)
If it does not work, try this one:
TRANSFORM Last(Qry_GA_Status.status) AS LastOfstatus
SELECT q.[Employee ID]
FROM 
(SELECT Employees.[Employee ID], [Employee Training Course Details].[Course number] FROM Employees, [Employee Training Course Details]) q LEFT JOIN Qry_GA_Status ON q.[Employee ID] = Qry_GA_Status.[Employee ID] AND q.[Course number] = Qry_GA_Status.[Course Number]
GROUP BY q.[Employee ID]
PIVOT q.[Course Details];

Open in new window

mickferrariICT Technician

Author

Commented:
Hello Chaau,

Unfortantley when I try your SQL I get the error: Syntax error in JOIN operation.

it then highlights "Employees" in the SELECT statement.

SELECT Employees.[Employee ID]
Top Expert 2013

Commented:
What about the second one I have provided?
TRANSFORM Last(Qry_GA_Status.status) AS LastOfstatus
SELECT q.[Employee ID]
FROM 
(SELECT Employees.[Employee ID], [Employee Training Course Details].[Course number] FROM Employees, [Employee Training Course Details]) q LEFT JOIN Qry_GA_Status ON q.[Employee ID] = Qry_GA_Status.[Employee ID] AND q.[Course number] = Qry_GA_Status.[Course Number]
GROUP BY q.[Employee ID]
PIVOT q.[Course Details];

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

mickferrariICT Technician

Author

Commented:
The second SQL code you supplied shows the following error:

The Microsoft Access database engine does not recognize 'q.[course details]' as a valid field name or expression.
Top Expert 2013
Commented:
You are right, my fault, forgot to include it to the "q". Try this one:
TRANSFORM Last(Qry_GA_Status.status) AS LastOfstatus
SELECT q.[Employee ID]
FROM 
(SELECT Employees.[Employee ID], 
 [Employee Training Course Details].[Course number],
 [Employee Training Course Details].[Course Details]
 FROM Employees, [Employee Training Course Details]) 
q LEFT JOIN Qry_GA_Status 
  ON q.[Employee ID] = Qry_GA_Status.[Employee ID] AND q.[Course number] = Qry_GA_Status.[Course Number]
GROUP BY q.[Employee ID]
PIVOT q.[Course Details];

Open in new window

mickferrariICT Technician

Author

Commented:
That worked! Thank you.

I need to do a bit of testing but I think that solved my issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial