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_Name'. 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_PROJ_MONTHLY_REPORT.PROJ_ID, ADMUSER_PUC_PROJ_MONTHLY_REPORT.PROJ_SHORT_NAME, ADMUSER_PUC_PROJ_MONTHLY_REPORT.PROJ_NAME, ADMUSER_PUC_WBS_BUDGETED_COSTS.WBS_CONCAT_NAME, Sum(ADMUSER_PUC_WBS_BUDGETED_COSTS.ORIG_BUDGETED_C OST) AS [PM Budget]
FROM ADMUSER_PUC_PROJ_MONTHLY_REPORT INNER JOIN ADMUSER_PUC_WBS_BUDGETED_COSTS ON ADMUSER_PUC_PROJ_MONTHLY_REPORT.PROJ_ID = ADMUSER_PUC_WBS_BUDGETED_COSTS.PROJ_ID
WHERE (((ADMUSER_PUC_WBS_BUDGETED_COSTS.WBS_NAME)="Proje ct Management"))
GROUP BY ADMUSER_PUC_PROJ_MONTHLY_REPORT.PROJ_ID, ADMUSER_PUC_PROJ_MONTHLY_REPORT.PROJ_SHORT_NAME, ADMUSER_PUC_PROJ_MONTHLY_REPORT.PROJ_NAME, ADMUSER_PUC_WBS_BUDGETED_COSTS.WBS_CONCAT_NAME;

The data in the field 'WBS_CONCAT_NAME' 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&AwardPMBudget, CL into closeout budget, etc. Here's what I tried:

SELECT [B - P3e PM Budgets].PROJ_ID, [B - P3e PM Budgets].PROJ_SHORT_NAME, [B - P3e PM Budgets].PROJ_NAME, Sum((SELECT [PM Budget] FROM [B - P3e PM Budgets] WHERE ((([B - P3e PM Budgets].WBS_CONCAT_NAME) Like '*DS*')))) AS [PM Budget]
FROM [B - P3e PM Budgets]
GROUP BY [B - P3e PM Budgets].PROJ_ID, [B - P3e PM Budgets].PROJ_SHORT_NAME, [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&AwardPMBudget,
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&AwardPMBudget,
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.