Calculate after Grouping

[leejayd]

I have the Group By query below :

SELECT "Price" AS CALC_KPI, tblGroup.GroupName, tblData.Time,
Sum([tblData].[Value]/[tblData_1].[Value]) AS [Value]
FROM (tblGroup INNER JOIN tblRegion ON tblGroup.GroupID =
tblRegion.GroupID) INNER JOIN (tblPlants INNER JOIN (tblData INNER
JOIN tblData AS tblData_1 ON (tblData.Time = tblData_1.Time) AND
(tblData.PlantID = tblData_1.PlantID) AND (tblData.VersionID =
tblData_1.VersionID)) ON tblPlants.PlantID = tblData.PlantID) ON
tblRegion.RegionID = tblPlants.RegionID
GROUP BY "Price", tblGroup.GroupName, tblData.Time, tblData.VersionID,
tblData.LineItemID, tblData_1.LineItemID
HAVING (((tblData.VersionID)=1) AND ((tblData.LineItemID)=2) AND
((tblData_1.LineItemID)=1));


In this query I am calculating a Price KPI (Sum([tblData].[Value]/
[tblData_1].[Value]) AS [Value])

Using the LineItems referenced in the HAVING clause.

My problem is that I want the calculation to divide after the records
have been aggregated. Instread I seem to be only able to Aggregate
after the Division hence I get prices added together which is wrong.
Is there a solution to this problem?

 

[Marshall Barton]

I have the Group By query below :

SELECT "Price" AS CALC_KPI, tblGroup.GroupName, tblData.Time,
Sum([tblData].[Value]/[tblData_1].[Value]) AS [Value]
FROM (tblGroup INNER JOIN tblRegion ON tblGroup.GroupID =
tblRegion.GroupID) INNER JOIN (tblPlants INNER JOIN (tblData INNER
JOIN tblData AS tblData_1 ON (tblData.Time = tblData_1.Time) AND
(tblData.PlantID = tblData_1.PlantID) AND (tblData.VersionID =
tblData_1.VersionID)) ON tblPlants.PlantID = tblData.PlantID) ON
tblRegion.RegionID = tblPlants.RegionID
GROUP BY "Price", tblGroup.GroupName, tblData.Time, tblData.VersionID,
tblData.LineItemID, tblData_1.LineItemID
HAVING (((tblData.VersionID)=1) AND ((tblData.LineItemID)=2) AND
((tblData_1.LineItemID)=1));


In this query I am calculating a Price KPI (Sum([tblData].[Value]/
[tblData_1].[Value]) AS [Value])

Using the LineItems referenced in the HAVING clause.

My problem is that I want the calculation to divide after the records
have been aggregated. Instread I seem to be only able to Aggregate
after the Division hence I get prices added together which is wrong.

I this what you want?

Sum([tblData].[Value]) / Sum([tblData_1].[Value]) AS [Value]

Whether that's it or not, the HAVING clause should be a
WHERE clause (stupid query designer).