Access SQL crosstab with multiple outer joins

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
mickferrariICT TechnicianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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

0
mickferrariICT TechnicianAuthor 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]
0
chaauCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mickferrariICT TechnicianAuthor 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.
0
chaauCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mickferrariICT TechnicianAuthor Commented:
That worked! Thank you.

I need to do a bit of testing but I think that solved my issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.