# Questioncrosstab query to chart, unrecognized field (1 Viewer)

#### john33john3

##### New member
I have three table ElectricityUser, ElectricityUsage, ElectricityEmissionFactor
ElectricityUser:
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:
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
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.

Sorry if wrong forum and dummy info

#### john33john33

##### Registered User
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

• 268.8 KB Views: 44

#### spikepl

##### Eledittingent Beliped
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
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
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
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
You can create a table from the result of the query, and use that table for the graph.

#### Attachments

• 34.9 KB Views: 32

#### spikepl

##### Eledittingent Beliped
@JHB I was thinking along those lines, but I 'm curious as to what the actual problem is. Any idea?

#### john33john33

##### Registered User
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?

#### john33john33

##### Registered User
O... I find that.
sorry for a dummy question.
query>Design>create table

#### JHB

##### Have been here a while
@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.
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
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

#### Attachments

• 53.3 KB Views: 26

#### john33john33

##### Registered User
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?