PivotChart how to show data bars using PIVOT SQL?
Greetings.
I am trying to create a PIVOT Chart in form and allow the users to select filering (e.g. date from and to). I was able to make on easily using Query Design -> PIVOT Chart View but this doesn't allow the users to filter.
This is my table as an example:
Month | Picture_Type | Count
Jan Cars 100
Jan Fruits 110
Jan Laptops 120
Jan Bicycles 130
Feb Cars 200
Feb Fruits 210
Feb Laptops 220
Feb Bicycles 230
Link to end result chart in a form:
http://imgur.com/7kOULJU
It should be grouped by month and with 4 data bars (Cars, Fruits, Laptops, Bicycles)
I think this can be done even just using PIVOT in SQL but I couldn't find any good examples and been doing trial and error with no result. Can somebody please help?
Basically I have a button onclick event which triggers the graph.RowSource based on the SQL
Currently my SQL shows the desired chart with data bars grouped by month but I want to show the Picture_Type just below these bars. How do I achieve this with the existing SQL I have below?
TRANSFORM SUM(A.Counts) AS Picture_Counts
SELECT A.Month, A.Picture_Type
FROM Table1 AS A
GROUP BY A.Month
PIVOT A.Picture_Type
Greetings.
I am trying to create a PIVOT Chart in form and allow the users to select filering (e.g. date from and to). I was able to make on easily using Query Design -> PIVOT Chart View but this doesn't allow the users to filter.
This is my table as an example:
Month | Picture_Type | Count
Jan Cars 100
Jan Fruits 110
Jan Laptops 120
Jan Bicycles 130
Feb Cars 200
Feb Fruits 210
Feb Laptops 220
Feb Bicycles 230
Link to end result chart in a form:
http://imgur.com/7kOULJU
It should be grouped by month and with 4 data bars (Cars, Fruits, Laptops, Bicycles)
I think this can be done even just using PIVOT in SQL but I couldn't find any good examples and been doing trial and error with no result. Can somebody please help?
Basically I have a button onclick event which triggers the graph.RowSource based on the SQL
Currently my SQL shows the desired chart with data bars grouped by month but I want to show the Picture_Type just below these bars. How do I achieve this with the existing SQL I have below?
TRANSFORM SUM(A.Counts) AS Picture_Counts
SELECT A.Month, A.Picture_Type
FROM Table1 AS A
GROUP BY A.Month
PIVOT A.Picture_Type
Last edited:
