[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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
0
mickferrari
Asked:
mickferrari
  • 3
  • 3
1 Solution
 
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
 
mickferrariAuthor 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
mickferrariAuthor 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
 
mickferrariAuthor Commented:
That worked! Thank you.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now