Nested join problem

[kman42]

I have a single table that I am trying to extract some information from. I had previously used two queries, with one referencing the other as follows:



Project_IDs:

SELECT Applications.[Project ID]
FROM Applications
WHERE (((Applications.[Grant Num Appl])="2") AND ((Applications.[Grant Num Supp Yr])>"2") AND ((Applications.[Grant Num Act])="R44") AND ((Applications.[Stat Grp])="A")) OR (((Applications.[Grant Num Appl])="2") AND ((Applications.[Grant Num Supp Yr])>"2") AND ((Applications.[Grant Num Act])="R42") AND ((Applications.[Stat Grp])="A"))
GROUP BY Applications.[Project ID];

SELECT [Grant Num IC] & [Grant Num Serial] AS [Project ID], Last(Applications.Institution) AS Institution, Last(Applications.[PI Name (Contact)]) AS [PI Name (Contact)], Last(Applications.Title) AS Title, Last(Applications.[Proj Start]) AS [Proj Start], Last(Applications.[Proj End]) AS [Proj End], Last(Applications.[PI Phone]) AS [PI Phone], Last(Applications.[PI Email]) AS [PI Email], Last(Applications.[PI Fax]) AS [PI Fax], Last(Applications.[PI Addr]) AS [PI Addr], IIf(Sum(IIf([Applications].[Active]="Y",1,0)>0),"Y","N") AS Active, IIf(Sum(IIf([Applications].[Stat Grp]="A",1,0)>0),"Y","N") AS [Stat Grp], Last(Applications.[RFA Title]) AS [RFA Title], Sum(Applications.[Awd Tot $ by IC]) AS [SumOfAwd Tot $ by IC], Last(Applications.Remarks) AS Remarks, Last(Applications.FPR) AS FPR
FROM Project_IDs INNER JOIN Applications ON Project_IDs.[Project ID]=Applications.[Project ID]
GROUP BY [Grant Num IC] & [Grant Num Serial]
HAVING (((IIf(Sum(IIf([Applications].[Stat Grp]="A",1,0)>0),"Y","N"))="Y"));

I would really like this to be in a single query, so I am trying to nest it as follows:

SELECT [Grant Num IC] & [Grant Num Serial] AS [Project ID], Last(Applications.Institution) AS Institution, Last(Applications.[PI Name (Contact)]) AS [PI Name (Contact)], Last(Applications.Title) AS Title, Last(Applications.[Proj Start]) AS [Proj Start], Last(Applications.[Proj End]) AS [Proj End], Last(Applications.[PI Phone]) AS [PI Phone], Last(Applications.[PI Email]) AS [PI Email], Last(Applications.[PI Fax]) AS [PI Fax], Last(Applications.[PI Addr]) AS [PI Addr], IIf(Sum(IIf([Applications].[Active]="Y",1,0)>0),"Y","N") AS Active, IIf(Sum(IIf([Applications].[Stat Grp]="A",1,0)>0),"Y","N") AS [Stat Grp], Last(Applications.[RFA Title]) AS [RFA Title], Sum(Applications.[Awd Tot $ by IC]) AS [SumOfAwd Tot $ by IC], Last(Applications.Remarks) AS Remarks, Last(Applications.FPR) AS FPR
FROM ((SELECT Applications.[Project ID] as p
FROM Applications
WHERE (((Applications.[Grant Num Appl])="2") AND ((Applications.[Grant Num Supp Yr])>"2") AND ((Applications.[Grant Num Act])="R44") AND ((Applications.[Stat Grp])="A")) OR (((Applications.[Grant Num Appl])="2") AND ((Applications.[Grant Num Supp Yr])>"2") AND ((Applications.[Grant Num Act])="R42") AND ((Applications.[Stat Grp])="A"))
GROUP BY Applications.[Project ID]) INNER JOIN Applications ON p=Applications.[Project ID])
GROUP BY [Grant Num IC] & [Grant Num Serial]
HAVING (((IIf(Sum(IIf([Applications].[Stat Grp]="A",1,0)>0),"Y","N"))="Y"));

I keep getting "JOIN expression not supported"

I realize this is a complicated set of queries and probably not the best way to do this, but I am very new to Access and SQL so I am just trying to get it working. Any help would be greatly appreciated.

[orange]

I agree it's a complex query. And none of us has any knowledge of your situation or what exactly you are trying to accomplish.
Have you tried running simpler queries and get intermediate results?
Do you have any test records such that you know that parts of this query are working correctly?

I have reformatted your query (trying to simplify it) but I may have missed something.
I'm surprised with the number of immediate iif's.

SELECT [Grant Num IC] & [Grant Num Serial] AS [Project ID]
, Last(Applications.Institution) AS Institution
, Last(Applications.[PI Name (Contact)]) AS [PI Name (Contact)]
, Last(Applications.Title) AS Title
, Last(Applications.[Proj Start]) AS [Proj Start]
, Last(Applications.[Proj End]) AS [Proj End]
, Last(Applications.[PI Phone]) AS [PI Phone]
, Last(Applications.[PI Email]) AS [PI Email]
, Last(Applications.[PI Fax]) AS [PI Fax]
, Last(Applications.[PI Addr]) AS [PI Addr]
, IIf(Sum(IIf([Applications].[Active]="Y",1,0)>0),"Y","N") AS Active
, IIf(Sum(IIf([Applications].[Stat Grp]="A",1,0)>0),"Y","N") AS [Stat Grp]
, Last(Applications.[RFA Title]) AS [RFA Title]
, Sum(Applications.[Awd Tot $ by IC]) AS [SumOfAwd Tot $ by IC]
, Last(Applications.Remarks) AS Remarks
, Last(Applications.FPR) AS FPR
FROM ((SELECT Applications.[Project ID] as p
FROM Applications
WHERE Applications.[Grant Num Appl]="2" AND
Applications.[Grant Num Supp Yr])>"2" AND
Applications.[Stat Grp])="A" AND
(Applications.[Grant Num Act]="R44" OR Applications.[Grant Num Act])="R42")
GROUP BY Applications.[Project ID])
INNER JOIN Applications ON p=Applications.[Project ID])
GROUP BY [Grant Num IC] & [Grant Num Serial]
HAVING (((IIf(Sum(IIf([Applications].[Stat Grp]="A",1,0)>0),"Y","N"))="Y"));

Good luck.

[kman42]

Thanks, I just solved the problem. I had to assign the internal query using the AS function. So:

(SELECT ...) AS InnerQuery INNER JOIN OtherQuery ON InnerQuery.Field=OtherQuery.Field