Question Regarding a Query

[aroraaj]

I am using the following SQL Statement for a query:

SELECT Sum(IIf([Development Source]=[P1 Production Source],[Target Cost],0)) AS CriteriaCost, Sum(qry_main_tbl_data.[Target Cost]) AS [SumOfTotal Cost], [CriteriaCost]/[SumOfTotal Cost] AS Percent_Development_Source, qry_main_tbl_data.[Engine Model]
FROM qry_main_tbl_data
GROUP BY qry_main_tbl_data.[Engine Model]
HAVING (((qry_main_tbl_data.[Engine Model]) Is Not Null));

My question is that for the Engine Model there are four different results. I have 810, 810/MRJ Common, BA, MRJ. Is there a way to add the data for 810/MRJ Common to 810 and MRJ and in the final results I only have three engine model which will be 810, BA and MRJ.

 

[Guus2005]

To increase readability you should split this problem in 2.

First group the engine 810 and 810/MRJ Common together like this

SELECT IIf([Development Source]=[P1 Production Source],[Target Cost],0) as CriteriaCost, qry_main_tbl_data.[Target Cost] as TargetCost, IIf(qry_main_tbl_data.[Engine Model]="810/MRJ Common","810",qry_main_tbl_data.[Engine Model]) AS EngineModel
FROM qry_main_tbl_data
WHERE (((qry_main_tbl_data.[Engine Model]) Is Not Null));

Store this query and name it "qryPart1" or whatever.

Now use group by on the qryPart1 query. To get the end result.

SELECT Sum(qryPart1.CriteriaCost) AS CriteriaCost, Sum(qryPart1.TargetCost) AS [SumOfTotalCost], [CriteriaCost]/[SumOfTotalCost] AS Percent_Development_Source, qryPart1.EngineModel
FROM qryPart1
GROUP BY qryPart1.EngineModel

These queries might include typo's.

HTH