charting - time axis

[Claus Haslauer]

Hi,
I want to create a chart
value (y-axis) vs. time (x-axis). In fact very similar to the
xy(scatter) example in the graph10.chm help-file.

I ran into problems, so I started a very basic *.mdb file.

It contains one table with ID, Date, and Price (Value) Column
ID is automatically assigned
Date is in the short date format
and Price is a long integer
I entered 10 datasets.

Then I created a query (the simplest one I could imagine)
SELECT tbl_Basis.Date, tbl_Basis.Price
FROM tbl_Basis
ORDER BY tbl_Basis.Date;

Then I created a form and inserted a "MS Graph Chart" Object, and set
the row source to the query. I changed the chart type to scatter, and
the number format of the x-axis to Date ("14/03/2001").

Now the problem: When I execute the form, all my data points are on
01/01/1900 and have different colours. When I change the x-axis number
format to general, they are all at "1".

Why would that be? I tried setting lond date instead of number format in
the table and in the properties of the query I set different number
formats - nothing has changed.

I appreciate any help,
thanks,
Claus

[David Schofield]

Re: charting - time axis

On Sun, 10 Jul 2005 14:42:24 -0400, Claus Haslauer
<chaslaue@scima il.uwaterloo.ca > wrote:
[color=blue]
>Hi,
>I want to create a chart
>value (y-axis) vs. time (x-axis). In fact very similar to the
>xy(scatter) example in the graph10.chm help-file.
>
>I ran into problems, so I started a very basic *.mdb file.
>
>It contains one table with ID, Date, and Price (Value) Column
>ID is automatically assigned
>Date is in the short date format
>and Price is a long integer
>I entered 10 datasets.
>
>Then I created a query (the simplest one I could imagine)
>SELECT tbl_Basis.Date, tbl_Basis.Price
>FROM tbl_Basis
>ORDER BY tbl_Basis.Date;
>
>Then I created a form and inserted a "MS Graph Chart" Object, and set
>the row source to the query. I changed the chart type to scatter, and
>the number format of the x-axis to Date ("14/03/2001").
>
>Now the problem: When I execute the form, all my data points are on
>01/01/1900 and have different colours. When I change the x-axis number
>format to general, they are all at "1".
>
>Why would that be? I tried setting lond date instead of number format in
>the table and in the properties of the query I set different number
>formats - nothing has changed.
>
>I appreciate any help,
>thanks,
>Claus[/color]
Hi
Few questions about charts get answered in this group, maybe it is a
black art only understood by Excel users. I hope this reply doesn't
put off anyone more expert answering you.

The problem is that charts are based on totals queries and regard
plotting an ordinary X-Y graph as some kind of PhD project.

I usually introduce an exta variable in the data which always has the
value 1, and then make the chart regard this as one of a series of
values to be plotted.

You can use a query like that below as source for a scatter plot:

TRANSFORM Max(tbl_Basis.p rice) AS MaxOfPrice SELECT
(Format([mydate],"mmm"" '""yy")) AS Expr1 FROM tbl_Basis GROUP BY
(Year([mydate])*12+Month([mydate])-1), tbl_Basis.mydat e,
(Format([mydate],"mmm"" '""yy")) PIVOT 1;

If you look at the output of the query you can see what input the
chart expects.

I have used mydate as a table field as I don't like to use reserved
words. Not the extra date in the group by in case there are two items
in one month. If there are two with the same date it will just show
the biggest. Of course if you prefer you can use SumOf rather than
MaxOf and just group by month or whatever.

HTH
David

[David Schofield]

Re: charting - time axis

On 11 Jul 2005 05:00:02 -0500, not@here (David Schofield) wrote:

NO NO NO to my previous post ! Think twice, post once.
See the corrected SQL below
David
[color=blue]
>On Sun, 10 Jul 2005 14:42:24 -0400, Claus Haslauer
><chaslaue@scim ail.uwaterloo.c a> wrote:
>[color=green]
>>Hi,
>>I want to create a chart
>>value (y-axis) vs. time (x-axis). In fact very similar to the
>>xy(scatter) example in the graph10.chm help-file.
>>
>>I ran into problems, so I started a very basic *.mdb file.
>>
>>It contains one table with ID, Date, and Price (Value) Column
>>ID is automatically assigned
>>Date is in the short date format
>>and Price is a long integer
>>I entered 10 datasets.
>>
>>Then I created a query (the simplest one I could imagine)
>>SELECT tbl_Basis.Date, tbl_Basis.Price
>>FROM tbl_Basis
>>ORDER BY tbl_Basis.Date;
>>
>>Then I created a form and inserted a "MS Graph Chart" Object, and set
>>the row source to the query. I changed the chart type to scatter, and
>>the number format of the x-axis to Date ("14/03/2001").
>>
>>Now the problem: When I execute the form, all my data points are on
>>01/01/1900 and have different colours. When I change the x-axis number
>>format to general, they are all at "1".
>>
>>Why would that be? I tried setting lond date instead of number format in
>>the table and in the properties of the query I set different number
>>formats - nothing has changed.
>>
>>I appreciate any help,
>>thanks,
>>Claus[/color]
>Hi
>Few questions about charts get answered in this group, maybe it is a
>black art only understood by Excel users. I hope this reply doesn't
>put off anyone more expert answering you.
>
>The problem is that charts are based on totals queries and regard
>plotting an ordinary X-Y graph as some kind of PhD project.
>
>I usually introduce an exta variable in the data which always has the
>value 1, and then make the chart regard this as one of a series of
>values to be plotted.
>
>You can use a query like that below as source for a scatter plot:
>[/color]
TRANSFORM Max(tbl_Basis.p rice) AS MaxOfPrice SELECT tbl_Basis.mydat e
FROM tbl_Basis GROUP BY tbl_Basis.mydat e PIVOT 1;[color=blue]
>
>If you look at the output of the query you can see what input the
>chart expects.
>
>I have used mydate as a table field as I don't like to use reserved
>words.
>iIf there are two items with the same date it will just show
>the biggest. Of course if you prefer you can use SumOf rather than
>MaxOf and just group by month or whatever.
>
>HTH
>David
>[/color]
Hi
NO NO NO to my previous post ! Think twice, post once.
See the corrected SQL above

David

[Claus Haslauer]

Re: charting - time axis

Hey David,
thanks a lot!
The tip with the crosstabs works nicely.

@all:
one thing I found out about continous timeline on the x-axis
If it is linear from a beginning to end or if it only shows the values
in the rowsource, seems to depend on the way the date is format.

Format([MyDate],"yyyy mm") results in a axis where each value is shown
at the same distance, even if they are differently long apart
Format([MyDate],"mmm yy") results in a linear time axis from the first
date to the last date in the rowsource.

Is there a reson behind that?


David Schofield wrote:[color=blue]
> On 11 Jul 2005 05:00:02 -0500, not@here (David Schofield) wrote:
>
> NO NO NO to my previous post ! Think twice, post once.
> See the corrected SQL below
> David
>
>[color=green]
>>On Sun, 10 Jul 2005 14:42:24 -0400, Claus Haslauer
>><chaslaue@sci mail.uwaterloo. ca> wrote:
>>
>>[color=darkred]
>>>Hi,
>>>I want to create a chart
>>>value (y-axis) vs. time (x-axis). In fact very similar to the
>>>xy(scatter ) example in the graph10.chm help-file.
>>>
>>>I ran into problems, so I started a very basic *.mdb file.
>>>
>>>It contains one table with ID, Date, and Price (Value) Column
>>>ID is automatically assigned
>>>Date is in the short date format
>>>and Price is a long integer
>>>I entered 10 datasets.
>>>
>>>Then I created a query (the simplest one I could imagine)
>>>SELECT tbl_Basis.Date, tbl_Basis.Price[/color]
>>[color=darkred]
>>>FROM tbl_Basis[/color]
>>[color=darkred]
>>>ORDER BY tbl_Basis.Date;
>>>
>>>Then I created a form and inserted a "MS Graph Chart" Object, and set
>>>the row source to the query. I changed the chart type to scatter, and
>>>the number format of the x-axis to Date ("14/03/2001").
>>>
>>>Now the problem: When I execute the form, all my data points are on
>>>01/01/1900 and have different colours. When I change the x-axis number
>>>format to general, they are all at "1".
>>>
>>>Why would that be? I tried setting lond date instead of number format in
>>>the table and in the properties of the query I set different number
>>>formats - nothing has changed.
>>>
>>>I appreciate any help,
>>>thanks,
>>>Claus[/color]
>>
>>Hi
>>Few questions about charts get answered in this group, maybe it is a
>>black art only understood by Excel users. I hope this reply doesn't
>>put off anyone more expert answering you.
>>
>>The problem is that charts are based on totals queries and regard
>>plotting an ordinary X-Y graph as some kind of PhD project.
>>
>>I usually introduce an exta variable in the data which always has the
>>value 1, and then make the chart regard this as one of a series of
>>values to be plotted.
>>
>>You can use a query like that below as source for a scatter plot:
>>[/color]
>
> TRANSFORM Max(tbl_Basis.p rice) AS MaxOfPrice SELECT tbl_Basis.mydat e
> FROM tbl_Basis GROUP BY tbl_Basis.mydat e PIVOT 1;
>[color=green]
>>If you look at the output of the query you can see what input the
>>chart expects.
>>
>>I have used mydate as a table field as I don't like to use reserved
>>words.
>>iIf there are two items with the same date it will just show
>>the biggest. Of course if you prefer you can use SumOf rather than
>>MaxOf and just group by month or whatever.
>>
>>HTH
>>David
>>[/color]
>
> Hi
> NO NO NO to my previous post ! Think twice, post once.
> See the corrected SQL above
>
> David
>
>
>[/color]