Nested Select - Help

I dont have a clue what i'm doing wrong.

SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members]
FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM

) AS qry_New_Members_HMO_All_Regions_1

GROUP BY qry_New_Members_HMO_All_Regions_1.reg
) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members]
FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM

) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
) AS qry_Termed_Contracts_HMO_All_Regions_1
GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
) AS qry_Termed_Members_HMO_All_Regions_1
GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg)
AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;

error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.I haven't a clue what you are doing right. Are you getting paid by the parenthesis?

"WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))"?

Isn't this:

"WHERE tbl_hmo_History.CONTRACT_NUM Is Null"

...simpler and easier to read?

And I suspect this may be throwing your first error:

"LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg,..."

The square brackets denote a database object. SELECT is a statement, not an object.

Clean up your code, format it well with indents, and try running the individual components separately before putting them all together. That is the best way to debug.|||it was working in access then i brought it over to sql and then MESS...

Are you getting paid by the parenthesis? -- haha i wish.

i will work through it again tomorrow.

thanks for looking at it.|||THAT was working in ACCESS?!

...but that explains the square brackets. I've had Access throw those into free SQL querys before, and then the query won't work until you take them out again. A bug, for sure.

Was it a single free SQL statement, or were the subqueries manifested as independent views?|||I got it to work.
PLEASE let me know if you see anything wrong with what i did.

SELECT dbo.Tbl_Region.REGION_NAME, [4_NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS],
[4_NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS],
[5_TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS],
[5_TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM dbo.Tbl_Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.CONTR ACT_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.CONTRACT_NUM
FROM tbl_HMO LEFT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.CONTRACT_NUM = tbl_HMO_History.CONTRACT_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.CONTRACT_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_CONTRACTS].Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.MEMBE R_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.MEMBER_NUM
FROM tbl_HMO Left JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.MEMBER_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_MEMBERS].Region Left OUTER JOIN
(SELECT qry_Termed_Contracts_HMO_All_Region_Names_1.Region ,
COUNT(qry_Termed_Contracts_HMO_All_Region_Names_1. CONTRACT_NUM) AS [# of Termed Contracts]
FROM (SELECT dbo.tbl_HMO_History.Region, dbo.tbl_HMO_History.Contract_Num
FROM dbo.tbl_HMO RIGHT OUTER JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
dbo.tbl_HMO.CONTRACT_NUM = dbo.tbl_HMO_History.Contract_Num
GROUP BY dbo.tbl_HMO_History.Region, dbo.tbl_HMO.CONTRACT_NUM, dbo.tbl_HMO_History.Contract_Num
HAVING (dbo.tbl_HMO.CONTRACT_NUM IS NULL)) AS qry_Termed_Contracts_HMO_All_Region_Names_1
GROUP BY qry_Termed_Contracts_HMO_All_Region_Names_1.Region ) [5_TERMED_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_CONTRACTS].Region Left OUTER JOIN
(SELECT qry_Termed_Members_HMO_All_Region_Names_1.Region, COUNT(qry_Termed_Members_HMO_All_Region_Names_1.ME MBER_NUM)
AS [# of Termed Members]
FROM (SELECT tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM
FROM tbl_HMO RIGHT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO.MEMBER_NUM IS NULL
GROUP BY tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM) AS qry_Termed_Members_HMO_All_Region_Names_1
GROUP BY qry_Termed_Members_HMO_All_Region_Names_1.Region) [5_TERMED_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_MEMBERS].Region