July 13, 2010 at 10:52 am
I have inherrited an access database which has a number of queries. I have tried to get this query to work and I keep getting errors.
SELECT [2010Projects].*, Director.DirectorName, Manager.ManagerName, ProjectManager.ProjectMgrName, ProjectReview.InitRevType, ProjectReview.RevDate, [2010AFE].*, Resources.*
FROM (ProjectReview INNER JOIN (ProjectManager INNER JOIN (Manager INNER JOIN (Director INNER JOIN (2010AFE INNER JOIN 2010Projects ON [2010AFE].AFE = [2010Projects].AFE) ON Director.DirectorID = [2010Projects].DirectorID) ON Manager.ManagerID = [2010Projects].ManagerID) ON ProjectManager.ProjectMgrID = [2010Projects].ProjectMgrID) ON ProjectReview.ID = [2010Projects].ProjectReviewID) INNER JOIN Resources ON [2010Projects].ProjectID = Resources.ProjectID
ORDER BY Director.DirectorName, ProjectManager.ProjectMgrName;
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
July 13, 2010 at 11:17 am
TSQL and Jet SQL handle joins a bit differently in terms of syntax. Here's the TSQL code for your join. I've put in "aliases" so references to specific tables are easier to create.
SELECT p.*, d.DirectorName, m.ManagerName, pm.ProjectMgrName, pr.InitRevType,
pr.RevDate, afe.*, r.*
FROM ProjectReview pr
INNER JOIN [2010Projects] p
on pr.ID = p.ProjectReviewID
INNER JOIN ProjectManager pm
on p.ProjectManagerID = pm.ProjectManagerID
INNER JOIN Manager m
on m.ManagerID = p.ManagerID
INNER JOIN Director d
on d.DirectorID = p.DirectorID
INNER JOIN [2010AFE] afe
on afe.AFE = p.AFE
INNER JOIN Resources r
on r.ProjectID = p.ProjectID
ORDER BY d.DirectorName, pm.ProjectMgrName;
July 13, 2010 at 11:19 am
Thanks, I solved my own question.
Here is what I did;
SELECT [2010AFE].AFE, [2010Projects].DirectorID, Director.DirectorID AS Expr1, [2010Projects].AFE AS Expr2, [2010Projects].ManagerID, Manager.ManagerID AS Expr3,
[2010Projects].ProjectMgrID, ProjectManager.ProjectMgrID AS Expr4, Resources.ProjectID, [2010Projects].ProjectReviewID, ProjectReview.ID
FROM [2010AFE] INNER JOIN
[2010Projects] ON [2010AFE].AFE = [2010Projects].AFE INNER JOIN
Director ON [2010Projects].DirectorID = Director.DirectorID INNER JOIN
Manager ON [2010Projects].ManagerID = Manager.ManagerID INNER JOIN
ProjectManager ON [2010Projects].ProjectMgrID = ProjectManager.ProjectMgrID INNER JOIN
Resources ON [2010Projects].ProjectID = Resources.ProjectID INNER JOIN
ProjectReview ON [2010Projects].ProjectReviewID = ProjectReview.ID
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply