Question crosstab query to chart, unrecognized field (1 Viewer)

john33john3

New member
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
 

john33john33

Registered User
Joined
Jul 23, 2015
Messages
18
thanks spikepl for replying
It seems "time" is not a reserved word because i did not get any message regarding reserved word when i made it a field name.
I did get the message when trying to use "date".

Whatever i tried your suggestion by changing that to "Occurrence" but it did not work.

attached with the database
 

Attachments

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
For the benefit of anyone else reading this:

I gave you a list of reserved words but for some weird reason you did not check it. Please do. Not getting warning does not mean a thing.

As to your remaining problem I'll have a look later. In the mean time, anybody else is welcome to it
 

john33john33

Registered User
Joined
Jul 23, 2015
Messages
18
For the benefit of anyone else reading this:

I gave you a list of reserved words but for some weird reason you did not check it. Please do. Not getting warning does not mean a thing.

As to your remaining problem I'll have a look later. In the mean time, anybody else is welcome to it
lesson learnt
I double checked the words and there is no reserved word.
And wounder why "time" works fine before for table entry and query as a reserved word?
 

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
I have had a look. Your graph is based on this query

Code:
TRANSFORM Sum(test.CO2) AS blahCO2
SELECT test.Year AS Expr1
FROM test
GROUP BY test.Year
PIVOT test.UserName;
and it is here that it freaks over ElectricityUsage.Occurence

I have no cure. Some more knowledgeable SQL people will be along soon, though.
 

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
You can create a table from the result of the query, and use that table for the graph.
 

Attachments

spikepl

Eledittingent Beliped
Joined
Nov 3, 2010
Messages
6,144
@JHB I was thinking along those lines, but I 'm curious as to what the actual problem is. Any idea?
 

john33john33

Registered User
Joined
Jul 23, 2015
Messages
18
You can create a table from the result of the query, and use that table for the graph.
thanks for the result.
And second with spikepl, why does it happen?
In my mind, three columns to be drawn to a chart, that seems to be ok.

Also with one question, did not see any delete or update in the SQL, how can it do so / where to see the settings?
 

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
@JHB I was thinking along those lines, but I 'm curious as to what the actual problem is. Any idea?
At that time it was a fast working solution, but in a way you "forced" me to find an answer. :D :D
It turned out to be the subquery which cause the problem, it shouldn't, but if the reference to the table is removed it runs okay.
Not working query:
Code:
SELECT ElectricityUser.UserName, Year(ElectricityUsage.[Occurrence]) AS [Year], Round(Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
                    WHERE year(ElectricityEmissionFactor.[Occurrence]) <= year([B][COLOR=Red]ElectricityUsage.[/COLOR][/B]Occurrence) order by ElectricityEmissionFactor.[Occurrence] desc
                    )),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE (((ElectricityUser.UserName)<>"Whole Campus"))
GROUP BY ElectricityUser.UserName, Year(ElectricityUsage.Occurrence);
Working query:
Code:
SELECT ElectricityUser.UserName, Year(ElectricityUsage.[Occurrence]) AS [Year], Round(Sum(ElectricityUsage.amount*(SELECT TOP 1 CO2Emission from ElectricityEmissionFactor
                    WHERE year(ElectricityEmissionFactor.[Occurrence]) <= year(Occurrence) order by ElectricityEmissionFactor.[Occurrence] desc
                    )),0) AS CO2
FROM ElectricityUser INNER JOIN ElectricityUsage ON ElectricityUser.UserID = ElectricityUsage.UserID
WHERE (((ElectricityUser.UserName)<>"Whole Campus"))
GROUP BY ElectricityUser.UserName, Year(ElectricityUsage.Occurrence);
 

Attachments

john33john33

Registered User
Joined
Jul 23, 2015
Messages
18
It works fine
thanks

but I don't understand the mechanism.
As ElectricityUsage.Occurrence => Occurrence works,
it seems to mean ElectricityUsage.Occurrence and Occurrence refer to two different stuff.
Then, what does Occurence refer to?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom