CODEWITHSUNDEEP
c#ms-access
Lausn
Lausn

Reputation: 45

Joining PIVOT table with data from two other tables with ACCESS

I'm having trouble with joining a pivot table with data from two other tables. The pivot table works fine but when I join it it says 'Syntax error in FROM clause'

SELECT r.resourceName AS 'Employee Name',
p.projectNumber AS 'Project Number',
p.projectSystem AS 'Project System',
p.projectManager AS 'Project Manager',
a.projectName AS 'Project Name'
FROM Projects p
LEFT JOIN 
(TRANSFORM SUM(a.AllocationValue) 
SELECT r.ResourceName, a.ProjectName                                                     
FROM Resources r
GROUP BY a.ResourceName, a.ProjectName
PIVOT a.AllocationMonth IN ('June, 2014', 'July, 2014', 'August, 2014') q
ON p.resourceName = q.resourceName
WHERE p.projectName = a.projectName 
ORDER BY r.resourceName, a.projectName

Any help would be appreciated, thanks!

Upvotes: 0

Views: 193

Answers (2)

maxhugen
maxhugen

Reputation: 1944

It appears that you are joining an aliased subquery "p" to another aliased as "q". Therefore, your join "ON p.resourceName = r.resourceName" should be "ON p.resourceName = q.resourceName".

Both the WHERE and ORDER BY also appear incorrect.

Have you tried constructing individual queries in the query editor, saving them, then constructing the final query? Examining the resultant sql (query > sql view) should help you.

Upvotes: 1

Jim
Jim

Reputation: 6881

I don't think you can have a combination of implicit and explicit joins, and you are also aliasing both the table Projects as p, and the subquery as p.

Instead of FROM Projects p, Resources r, which is an implicit join, you should make that an explicit join FROM Projects p JOIN Resources r ON p.whatever = r.whatever, and then change the alias of your subquery so it isn't also named p.

You also have Resources aliased as r in both the outer query and the subquery, and that's a conflict.

Those are the issues I see right off the bat.

Upvotes: 1

Related Questions