使用ACCESS将PIVOT表与另外两个表中的数据连接起来

我在将数据透视表与另外两个表的数据连接起来时遇到了问题。透视表工作正常，但当我加入它时，它会显示"FROM子句中的语法错误"

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

任何帮助都将不胜感激，谢谢！