Query saves wrong

[Guest]

Okay, I'm used to using queries embedded in other queries, and when you go to
edit them you have to change the []. AS to () AS. It's flaky, but I can
accept that. But this is a new one. Does anyone have any idea why when I
save the following query in Access 2003 it saves funny.

Here's the query as it should be:

TRANSFORM Sum(PRW.Hours) AS SumOfHours
SELECT T1.RoleID, RL.Role, T1.Level
FROM ((SELECT R.ProjectID, R.RoleID, W.WeekStart, W.Level, W.ColumnAlias
FROM (SELECT DISTINCT ProjectID, RoleID FROM tmp_PRWR) AS R, (SELECT
DISTINCT WeekStart, [Level], ColumnAlias FROM tColumn_Alias) AS W) AS T1
INNER JOIN tCP_Role AS RL ON T1.RoleID = RL.RoleID) LEFT JOIN
tCP_Project_Role_Week AS PRW ON (T1.ProjectID = PRW.ProjectID) AND (T1.RoleID
= PRW.RoleID) AND (T1.WeekStart = PRW.WeekStart)
GROUP BY T1.RoleID, RL.Role, T1.Level
PIVOT T1.ColumnAlias In ("A","B","C","D","E","F","G","H","I","J","K","L","M");

Here's the query as it appears after I've saved the query and then re-enter
it:

TRANSFORM Sum(PRW.Hours) AS SumOfHours SELECT T1.RoleID, RL.Role, T1.Level
FROM ([SELECT R].[ProjectID, R].[RoleID, W].[WeekStart, W].[Level,
W].[ColumnAlias FROM (SELECT DISTINCT ProjectID, RoleID FROM tmp_PRWR) AS R,
(SELECT DISTINCT WeekStart, [Level], ColumnAlias FROM tColumn_Alias) AS W] AS
T1 INNER JOIN tCP_Role AS RL ON T1.RoleID=RL.RoleID) LEFT JOIN
tCP_Project_Role_Week AS PRW ON (T1.WeekStart=PRW.WeekStart) AND
(T1.RoleID=PRW.RoleID) AND (T1.ProjectID=PRW.ProjectID) GROUP BY T1.RoleID,
RL.Role, T1.Level PIVOT T1.ColumnAlias In
("A","B","C","D","E","F","G","H","I","J","K","L","M");

For some reason Access is putting brackets around the wrong parts. Does
anyone have any idea what is going on with this?

TIA

Mark

 

[John Spencer]

You CANNOT have [] inside the subquery. LEVEL has the square brackets
around it. Try dropping the square brackets and using the
tablename.fieldname combination.

TRANSFORM Sum(PRW.Hours) AS SumOfHours
SELECT T1.RoleID, RL.Role, T1.Level
FROM
((SELECT R.ProjectID, R.RoleID, W.WeekStart, W.Level, W.ColumnAlias
FROM (SELECT DISTINCT ProjectID, RoleID
FROM tmp_PRWR) AS R
, (SELECT DISTINCT WeekStart, tColumn_Alias.Level, ColumnAlias
FROM tColumn_Alias) AS W) AS T1
INNER JOIN tCP_Role AS RL ON T1.RoleID = RL.RoleID)
LEFT JOIN tCP_Project_Role_Week AS PRW
ON (T1.ProjectID = PRW.ProjectID)
AND (T1.RoleID = PRW.RoleID)
AND (T1.WeekStart = PRW.WeekStart)
GROUP BY T1.RoleID, RL.Role, T1.Level
PIVOT T1.ColumnAlias In
("A","B","C","D","E","F","G","H","I","J","K","L","M");

Even with that change I'm not sure that Access will let you have a nest a
subquery in the from clause of a subquery. But the first place I would
start would be by removing those brackets.

Beyond that the only way I can see would be to use stored queries in the
query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Okay, I'm used to using queries embedded in other queries, and when you go
to
edit them you have to change the []. AS to () AS. It's flaky, but I can
accept that. But this is a new one. Does anyone have any idea why when I
save the following query in Access 2003 it saves funny.

Here's the query as it should be:

TRANSFORM Sum(PRW.Hours) AS SumOfHours
SELECT T1.RoleID, RL.Role, T1.Level
FROM ((SELECT R.ProjectID, R.RoleID, W.WeekStart, W.Level, W.ColumnAlias
FROM (SELECT DISTINCT ProjectID, RoleID FROM tmp_PRWR) AS R, (SELECT
DISTINCT WeekStart, [Level], ColumnAlias FROM tColumn_Alias) AS W) AS T1
INNER JOIN tCP_Role AS RL ON T1.RoleID = RL.RoleID) LEFT JOIN
tCP_Project_Role_Week AS PRW ON (T1.ProjectID = PRW.ProjectID) AND
(T1.RoleID
= PRW.RoleID) AND (T1.WeekStart = PRW.WeekStart)
GROUP BY T1.RoleID, RL.Role, T1.Level
PIVOT T1.ColumnAlias In
("A","B","C","D","E","F","G","H","I","J","K","L","M");

Here's the query as it appears after I've saved the query and then
re-enter
it:

TRANSFORM Sum(PRW.Hours) AS SumOfHours SELECT T1.RoleID, RL.Role, T1.Level
FROM ([SELECT R].[ProjectID, R].[RoleID, W].[WeekStart, W].[Level,
W].[ColumnAlias FROM (SELECT DISTINCT ProjectID, RoleID FROM tmp_PRWR) AS
R,
(SELECT DISTINCT WeekStart, [Level], ColumnAlias FROM tColumn_Alias) AS W]
AS
T1 INNER JOIN tCP_Role AS RL ON T1.RoleID=RL.RoleID) LEFT JOIN
tCP_Project_Role_Week AS PRW ON (T1.WeekStart=PRW.WeekStart) AND
(T1.RoleID=PRW.RoleID) AND (T1.ProjectID=PRW.ProjectID) GROUP BY
T1.RoleID,
RL.Role, T1.Level PIVOT T1.ColumnAlias In
("A","B","C","D","E","F","G","H","I","J","K","L","M");

For some reason Access is putting brackets around the wrong parts. Does
anyone have any idea what is going on with this?

TIA

Mark