Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-22-2015, 08:01 PM   #1
john33john3
Newly Registered User
 
Join Date: Jul 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
john33john3 is on a distinguished road
crosstab query to chart, unrecognized field

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

john33john3 is offline   Reply With Quote
Old 07-22-2015, 10:36 PM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: crosstab query to chart, unrecognized field

time is a reserved word, http://allenbrowne.com/AppIssueBadWord.html try to rename or put it in square brackets
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
The Following User Says Thank You to spikepl For This Useful Post:
john33john33 (07-23-2015)
Old 07-22-2015, 11:19 PM   #3
john33john33
Newly Registered User
 
Join Date: Jul 2015
Posts: 18
Thanks: 6
Thanked 0 Times in 0 Posts
john33john33 is on a distinguished road
Re: crosstab query to chart, unrecognized field

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
Attached Files
File Type: zip CO2.zip (268.8 KB, 43 views)

john33john33 is offline   Reply With Quote
Old 07-23-2015, 12:33 AM   #4
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: crosstab query to chart, unrecognized field

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
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 07-23-2015, 12:47 AM   #5
john33john33
Newly Registered User
 
Join Date: Jul 2015
Posts: 18
Thanks: 6
Thanked 0 Times in 0 Posts
john33john33 is on a distinguished road
Re: crosstab query to chart, unrecognized field

Quote:
Originally Posted by spikepl View Post
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?
john33john33 is offline   Reply With Quote
Old 07-23-2015, 01:14 AM   #6
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: crosstab query to chart, unrecognized field

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.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 07-23-2015, 01:16 AM   #7
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,707
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: crosstab query to chart, unrecognized field

You can create a table from the result of the query, and use that table for the graph.
Attached Files
File Type: zip CO2.zip (34.9 KB, 31 views)

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
john33john33 (07-23-2015)
Old 07-23-2015, 01:18 AM   #8
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: crosstab query to chart, unrecognized field

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

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 07-23-2015, 05:47 PM   #9
john33john33
Newly Registered User
 
Join Date: Jul 2015
Posts: 18
Thanks: 6
Thanked 0 Times in 0 Posts
john33john33 is on a distinguished road
Re: crosstab query to chart, unrecognized field

Quote:
Originally Posted by JHB View Post
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 is offline   Reply With Quote
Old 07-23-2015, 07:02 PM   #10
john33john33
Newly Registered User
 
Join Date: Jul 2015
Posts: 18
Thanks: 6
Thanked 0 Times in 0 Posts
john33john33 is on a distinguished road
Re: crosstab query to chart, unrecognized field

O... I find that.
sorry for a dummy question.
query>Design>create table
john33john33 is offline   Reply With Quote
Old 07-25-2015, 10:53 PM   #11
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,707
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: crosstab query to chart, unrecognized field

Quote:
Originally Posted by spikepl View Post
@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(ElectricityUsage.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);
Attached Files
File Type: zip CO23.zip (53.3 KB, 25 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
john33john33 (07-26-2015)
Old 07-26-2015, 06:48 PM   #12
john33john33
Newly Registered User
 
Join Date: Jul 2015
Posts: 18
Thanks: 6
Thanked 0 Times in 0 Posts
john33john33 is on a distinguished road
Re: crosstab query to chart, unrecognized field

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?

john33john33 is offline   Reply With Quote
Reply

Tags
access2013 , charts

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use two value field in a crosstab query? Nicesoni_ash Queries 5 09-18-2013 03:07 AM
Unrecognized field name error smrtgi Queries 7 03-22-2013 09:55 AM
Crosstab Chart damian Modules & VBA 2 04-05-2011 05:40 AM
Bar chart Line/bars from crosstab HELP Singlespeed Reports 1 06-19-2009 03:02 AM
Chart Wizard from a crosstab query? chris.mo Forms 1 11-24-2001 02:13 PM




All times are GMT -8. The time now is 04:42 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World