L
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?
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?