john33john3
New member
- Local time
- Today, 13:03
- Joined
- Jul 23, 2015
- Messages
- 1
I have three table ElectricityUser, ElectricityUsage, ElectricityEmissionFactor
ElectricityUser:
UserID UserName
1 Main Building
2 Staff Quarter
ElectricityUsage:
UserID Time Amount
1 1/7/2010 23230
1 8/10/2011 34340
1 8/1/2011 34300
1 2/3/2012 43430
1 4/2/2013 43560
1 3/2/2014 44540
2 3/6/2014 44000
ElectricityEmissionFactor:
Time CO2Emission
1/1/2010 0.5
1/1/2011 0.55
1/1/2012 0.56
1/1/2013 0.57
And intended outcome:
UserName Time CO2
1 2010 11615
1 2011 37752 (34340*0.55 + 34300*0.55)
1 2012 24320.8
1 2013 24829.2
1 2014 25387.8
2 2014 25080
The logic is ElecUsage.Amount * ElecEmissionFactor. If same user and same year, add them up for the record of that year.
This query,
SELECT ElectricityUser.UserName, Year(ElectricityUsage.Time) AS [Year], Round(Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
WHERE year(ElectricityEmissionFactor.Time) <= year(ElectricityUsage.time) order by ElectricityEmissionFactor.Time desc
)),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
GROUP BY ElectricityUser.UserName, Year(ElectricityUsage.Time);
successfully extract the desired data.
Within the query, it works fine.
The result is with three column,UserName, Year, CO2
But when chart is tried to made, it prompts "ElectricityUsage.time unrecognized field.
The following is the chart formula:
TRANSFORM Sum([CO2]) AS [Total CO2] SELECT [Year] FROM [test] GROUP BY [Year] PIVOT [UserName];
Deseird outcome:
Y axis: CO2
X axis: Year e.g. 2010, 2011
Legend: user name
How come it is ok with query but not chart?
If only two fields from the query are chosen, the chart displays fine.
Thanks for any reply
Sorry if wrong forum and dummy info
ElectricityUser:
UserID UserName
1 Main Building
2 Staff Quarter
ElectricityUsage:
UserID Time Amount
1 1/7/2010 23230
1 8/10/2011 34340
1 8/1/2011 34300
1 2/3/2012 43430
1 4/2/2013 43560
1 3/2/2014 44540
2 3/6/2014 44000
ElectricityEmissionFactor:
Time CO2Emission
1/1/2010 0.5
1/1/2011 0.55
1/1/2012 0.56
1/1/2013 0.57
And intended outcome:
UserName Time CO2
1 2010 11615
1 2011 37752 (34340*0.55 + 34300*0.55)
1 2012 24320.8
1 2013 24829.2
1 2014 25387.8
2 2014 25080
The logic is ElecUsage.Amount * ElecEmissionFactor. If same user and same year, add them up for the record of that year.
This query,
SELECT ElectricityUser.UserName, Year(ElectricityUsage.Time) AS [Year], Round(Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
WHERE year(ElectricityEmissionFactor.Time) <= year(ElectricityUsage.time) order by ElectricityEmissionFactor.Time desc
)),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
GROUP BY ElectricityUser.UserName, Year(ElectricityUsage.Time);
successfully extract the desired data.
Within the query, it works fine.
The result is with three column,UserName, Year, CO2
But when chart is tried to made, it prompts "ElectricityUsage.time unrecognized field.
The following is the chart formula:
TRANSFORM Sum([CO2]) AS [Total CO2] SELECT [Year] FROM [test] GROUP BY [Year] PIVOT [UserName];
Deseird outcome:
Y axis: CO2
X axis: Year e.g. 2010, 2011
Legend: user name
How come it is ok with query but not chart?
If only two fields from the query are chosen, the chart displays fine.
Thanks for any reply
Sorry if wrong forum and dummy info