Subquery Help Needed

[dcourington]

I'm new to this group and not an experienced Access user, but have solved several of my problems already by reading other threads on this forum (Thanks!). Here's my current problem that I can't figure out how to solve:

I'm trying to calculate total project management budget by project phase. I have a query that returns data grouped by project and index code, and has the sum of the PM budgets along with a field called 'WBS_CONCAT_Nam e'. This name field specifies which phase of the project the budget belongs to (Design, Construction, Bid & Award, etc.). I now need to group and sum by that concatenated name field in order to get the PM budget by phase. Here's the query that extracts the project management budget:

SELECT ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_ID, ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_SHORT_NA ME, ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_NAME, ADMUSER_PUC_WBS _BUDGETED_COSTS .WBS_CONCAT_NAM E, Sum(ADMUSER_PUC _WBS_BUDGETED_C OSTS.ORIG_BUDGE TED_COST) AS [PM Budget]
FROM ADMUSER_PUC_PRO J_MONTHLY_REPOR T INNER JOIN ADMUSER_PUC_WBS _BUDGETED_COSTS ON ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_ID = ADMUSER_PUC_WBS _BUDGETED_COSTS .PROJ_ID
WHERE (((ADMUSER_PUC_ WBS_BUDGETED_CO STS.WBS_NAME)=" Project Management"))
GROUP BY ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_ID, ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_SHORT_NA ME, ADMUSER_PUC_PRO J_MONTHLY_REPOR T.PROJ_NAME, ADMUSER_PUC_WBS _BUDGETED_COSTS .WBS_CONCAT_NAM E;

The data in the field 'WBS_CONCAT_NAM E' looks like this:
CUW37801.BA.PM
CUW37801.CL.PM
CUW37801.CM.PM
CUW37801.DS.PM
CUW37801.ER.PM
CUW37801.PL.PM
CUW37901.BA.PM
CUW37901.CL.PM
CUW37901.CM.PM
CUW37901.DS.PM
CUW37901.ER.PM
CUW37901.PL.PM
CUW37201.BA.PM

I'm trying to write a subquery that looks at this string and sums all the budgets where the string contains 'BA' into a total called Bid&AwardPMBudg et, CL into closeout budget, etc. Here's what I tried:

SELECT [B - P3e PM Budgets].PROJ_ID, [B - P3e PM Budgets].PROJ_SHORT_NAM E, [B - P3e PM Budgets].PROJ_NAME, Sum((SELECT [PM Budget] FROM [B - P3e PM Budgets] WHERE ((([B - P3e PM Budgets].WBS_CONCAT_NAM E) Like '*DS*')))) AS [PM Budget]
FROM [B - P3e PM Budgets]
GROUP BY [B - P3e PM Budgets].PROJ_ID, [B - P3e PM Budgets].PROJ_SHORT_NAM E, [B - P3e PM Budgets].PROJ_NAME;

I get a message saying this query can return only one record.

I think my problem is that I can't just use a subquery and instead need to make a temp table? I have no idea if that will work or how to do it, and I also have no idea if there's a way to do this in the initial query. I didn't find info in help, or on the Microsoft website and would appreciate any advice. Thanks.

[MMcCarthy]

Try something like this:

SELECT Sum(IIf([B - P3e PM Budgets].[WBS_CONCAT_NAME] Like '*BA*',[B - P3e PM Budgets].[PM Budget],0)) As Bid&AwardPMBudg et,
Sum(IIf([B - P3e PM Budgets].[WBS_CONCAT_NAME] Like '*CL*',[B - P3e PM Budgets].[PM Budget],0)) As CloseoutBudget
FROM [B - P3e PM Budgets];

When you include other fields in your query the sum total you are trying to achieve is being broken down by those fields.

[dcourington]

Try something like this:

SELECT Sum(IIf([B - P3e PM Budgets].[WBS_CONCAT_NAME] Like '*BA*',[B - P3e PM Budgets].[PM Budget],0)) As Bid&AwardPMBudg et,
Sum(IIf([B - P3e PM Budgets].[WBS_CONCAT_NAME] Like '*CL*',[B - P3e PM Budgets].[PM Budget],0)) As CloseoutBudget
FROM [B - P3e PM Budgets];

When you include other fields in your query the sum total you are trying to achieve is being broken down by those fields.

THANK YOU!!!! I didn't even think of trying IIF but it worked perfectly.