Y
yussi1870
The problems is that I am trying to create a graph on a Form with MMM
YY as the X Axis. The data is based on a query of two queries with
the final result of the X Axis data in an expression. The causes the
graph to read the expression column as a string and so sorts the MMM
YY data alphabetically rather than by Month / Year. Any idea how I
can get it to sort correctly?
Here is the detail of exactly what is going on:
I am using Access 2002 but in the Access 2000 file format.
I have a table called t_Logs with the following fields:
Log_Number (PK, Text 10)
Date_Transferred (Date/Time - General Date, Required)
Transferred_To (Text 250, Required)
Each Log_Number represents a distinct log. Each distinct
Transferred_To value represents a group. I want a graph which shows
by month the count of logs for each group. These counts will be
represented by bars. On the same graph I want a count by month of the
total logs for the month. This count will be represented by a line.
To generate the data for this graph I have created three queries. The
first query, q_LogbyGroup, is a Crosstab query (whatever that means)
which gives me the count of logs by month for each group. Here is the
SQL:
TRANSFORM Count(*) AS [Count]
SELECT (Format([Date_Transferred],"mm/yy")) AS Expr1
FROM t_Logs
GROUP BY (Year([Date_Transferred])*12+Month([Date_Transferred])-1),(Format([Date_Transferred],"mm/yy"))
PIVOT t_Logs.Transferred_To;
The second query, q_LogsTotal, is a Select Query which gives me the
total count of logs by month. Here is the SQL:
SELECT (Format([Date_Transferred],"mm/yy")) AS Expr1, Count(*) AS
[Count]
FROM t_Logs
GROUP BY (Format([Date_Transferred],"mm/yy")),(Year([Date_Transferred])*12+Month([Date_Transferred])-1);
The third query, q_LogsTransferred, combines the other two queries to
create the data for the graph. It also has two extra date column to
sort by to force the Datasheet View to show the data sorted by month
correctly. This works. Here is the SQL:
SELECT Right(q_LogbyGroup.Expr1,2) AS yy, Left(q_LogbyGroup.Expr1,2)
AS mm, q_LogbyGroup.Expr1, q_LogbyGroup.[Group 1], q_LogbyGroup.[Group
2], q_LogsTotal.Count
FROM q_LogbyGroup INNER JOIN q_LogsTotal ON q_LogbyGroup.Expr1 =
q_LogsTotal.Expr1
ORDER BY Right(q_LogbyGroup.Expr1,2),Left(q_LogbyGroup.Expr1,2);
Then I create a form in Design View. I Insert a Chart which actives
the Chart Wizard. I change the View to Queries and choose
q_LogsTransferred. From the Available Fields I pick Expr1, Group 1,
Group 2 and Count. I choose the default Column Chart. In the Data
field I put Group 1, Group 2 below that and Count below that. For the
Axis I put Expr1. I leave Series blank. Then I enter a title for my
Graph and click Finish.
I then View the graph in Form View and sure enough the mmm/yy's (X
axis) are sorted in some strange manner with many of the dates not
exactly matching the data. Then, in design view, I view the Datasheet
of the Graph and the data is correct but is not sorted properly. When
I view the datasheet view of the q_LogsTransferred query it is sorted
correctly. How do I fix this?
YY as the X Axis. The data is based on a query of two queries with
the final result of the X Axis data in an expression. The causes the
graph to read the expression column as a string and so sorts the MMM
YY data alphabetically rather than by Month / Year. Any idea how I
can get it to sort correctly?
Here is the detail of exactly what is going on:
I am using Access 2002 but in the Access 2000 file format.
I have a table called t_Logs with the following fields:
Log_Number (PK, Text 10)
Date_Transferred (Date/Time - General Date, Required)
Transferred_To (Text 250, Required)
Each Log_Number represents a distinct log. Each distinct
Transferred_To value represents a group. I want a graph which shows
by month the count of logs for each group. These counts will be
represented by bars. On the same graph I want a count by month of the
total logs for the month. This count will be represented by a line.
To generate the data for this graph I have created three queries. The
first query, q_LogbyGroup, is a Crosstab query (whatever that means)
which gives me the count of logs by month for each group. Here is the
SQL:
TRANSFORM Count(*) AS [Count]
SELECT (Format([Date_Transferred],"mm/yy")) AS Expr1
FROM t_Logs
GROUP BY (Year([Date_Transferred])*12+Month([Date_Transferred])-1),(Format([Date_Transferred],"mm/yy"))
PIVOT t_Logs.Transferred_To;
The second query, q_LogsTotal, is a Select Query which gives me the
total count of logs by month. Here is the SQL:
SELECT (Format([Date_Transferred],"mm/yy")) AS Expr1, Count(*) AS
[Count]
FROM t_Logs
GROUP BY (Format([Date_Transferred],"mm/yy")),(Year([Date_Transferred])*12+Month([Date_Transferred])-1);
The third query, q_LogsTransferred, combines the other two queries to
create the data for the graph. It also has two extra date column to
sort by to force the Datasheet View to show the data sorted by month
correctly. This works. Here is the SQL:
SELECT Right(q_LogbyGroup.Expr1,2) AS yy, Left(q_LogbyGroup.Expr1,2)
AS mm, q_LogbyGroup.Expr1, q_LogbyGroup.[Group 1], q_LogbyGroup.[Group
2], q_LogsTotal.Count
FROM q_LogbyGroup INNER JOIN q_LogsTotal ON q_LogbyGroup.Expr1 =
q_LogsTotal.Expr1
ORDER BY Right(q_LogbyGroup.Expr1,2),Left(q_LogbyGroup.Expr1,2);
Then I create a form in Design View. I Insert a Chart which actives
the Chart Wizard. I change the View to Queries and choose
q_LogsTransferred. From the Available Fields I pick Expr1, Group 1,
Group 2 and Count. I choose the default Column Chart. In the Data
field I put Group 1, Group 2 below that and Count below that. For the
Axis I put Expr1. I leave Series blank. Then I enter a title for my
Graph and click Finish.
I then View the graph in Form View and sure enough the mmm/yy's (X
axis) are sorted in some strange manner with many of the dates not
exactly matching the data. Then, in design view, I view the Datasheet
of the Graph and the data is correct but is not sorted properly. When
I view the datasheet view of the q_LogsTransferred query it is sorted
correctly. How do I fix this?