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.