Query with complex joins (sub-query)

[bmcd]

I need to execute a complex query. I can get the query to work if I break it into two queries, queryA and queryB, and then use queryB inside queryA. Note that queryB (combined query) produces the final results I want.

Here is the code for queryA
---------------------------------
SELECT
Table_Subgrantees.ID,
Table_Subgrantees.SDPISubNumber,
Table_Subgrantees.SDPIName1 AS SDPINameSubgrantee,
Table_Subgrantees.SDPIName2 AS SDPIName2Subgrantee,
Table_FormLogInfoSubgrantees2004.FormRequired AS FormRequiredSubgrantee, Table_FormLogInfoSubgrantees2004.FormRequiredComments AS RequiredCommentsSubgrantee
FROM Table_Subgrantees INNER JOIN Table_FormLogInfoSubgrantees2004 ON Table_Subgrantees.SDPISubID = Table_FormLogInfoSubgrantees2004.SDPISubID;

Here is the code for queryB
--------------------------------
SELECT
Table_AreaAbbrs.AreaAbbr,
Table_AreaAbbrs.AreaID,
Table_Grantees.SDPINShort,
Table_Grantees.SDPINumber,
Table_FormLogInfoGrantees2004.FormRequired,
Table_FormLogInfoGrantees2004.FormRequiredComments,
Table_Grantees.SDPIName1,
Table_Grantees.SDPIName2,
QueryA.SDPISubNumber,
QueryA.SDPINameSubgrantee,
QueryA.SDPIName2Subgrantee,
QueryA.FormRequiredSubgrantee
FROM (Table_AreaAbbrs INNER JOIN (Table_Grantees LEFT JOIN QueryA ON Table_Grantees.ID = QueryA.ID) ON Table_AreaAbbrs.AreaID = Table_Grantees.AreaID) INNER JOIN Table_FormLogInfoGrantees2004 ON Table_Grantees.ID = Table_FormLogInfoGrantees2004.ID;

------------------------------------------------------------------------------------------------

I need to execute this query from ADO inside my code so I would like to come up with a single query that will return the results I want. I have played around with it for quite a while and everything I come up with generates messages like: "Join expression not supported".

Can anyone help me write this query as a single query?

Thank You,

Becky

 

[Alex McDevitt]

Not sure without looking at any data but will this work?

SELECT Table_AreaAbbrs.AreaAbbr, Table_AreaAbbrs.AreaID, Table_Grantees.SDPINShort, Table_Grantees.SDPINumber, Table_FormLogInfoGrantees2004.FormRequired, Table_FormLogInfoGrantees2004.FormRequiredComments, Table_Grantees.SDPIName1, Table_Grantees.SDPIName2, Table_Subgrantees.SDPISubNumber, Table_Subgrantees.SDPIName1 AS SDPINameSubgrantee, Table_Subgrantees.SDPIName2 AS SDPIName2Subgrantee, Table_FormLogInfoSubgrantees2004.FormRequired AS FormRequiredSubgrantee
FROM (((Table_AreaAbbrs INNER JOIN Table_Grantees ON Table_AreaAbbrs.AreaID = Table_Grantees.AreaID) INNER JOIN Table_FormLogInfoGrantees2004 ON Table_Grantees.id = Table_FormLogInfoGrantees2004.id) LEFT JOIN Table_Subgrantees ON Table_Grantees.id = Table_Subgrantees.ID) LEFT JOIN Table_FormLogInfoSubgrantees2004 ON Table_Subgrantees.SDPISubID = Table_FormLogInfoSubgrantees2004.SDPISubID;