asked on 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)
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
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)
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
Microsoft Access
Last Comment
mickferrari
ASKER
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]
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]
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];
ASKER
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.
The Microsoft Access database engine does not recognize 'q.[course details]' as a valid field name or expression.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That worked! Thank you.
I need to do a bit of testing but I think that solved my issue.
I need to do a bit of testing but I think that solved my issue.
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:
Open in new window