We help IT Professionals succeed at work.

Access SQL crosstab with multiple outer joins

mickferrari
mickferrari asked
on
274 Views
Last Modified: 2014-08-07
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

CERTIFIED EXPERT
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]
CERTIFIED EXPERT
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

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.
CERTIFIED EXPERT
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
mickferrariICT Technician

Author

Commented:
That worked! Thank you.

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions