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]

On Sun, 10 Jul 2005 14:42:24 -0400, Claus Haslauer
<ch******@scimail.uwaterloo.ca> wrote:

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

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.price) AS MaxOfPrice SELECT
(Format([mydate],"mmm"" '""yy")) AS Expr1 FROM tbl_Basis GROUP BY
(Year([mydate])*12+Month([mydate])-1), tbl_Basis.mydate,
(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]

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

On Sun, 10 Jul 2005 14:42:24 -0400, Claus Haslauer
<ch******@scimail.uwaterloo.ca> wrote:

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,
ClausHi
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.price) AS MaxOfPrice SELECT tbl_Basis.mydate
FROM tbl_Basis GROUP BY tbl_Basis.mydate 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.
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

Hi
NO NO NO to my previous post ! Think twice, post once.
See the corrected SQL above

David

[Claus Haslauer]

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:

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

On Sun, 10 Jul 2005 14:42:24 -0400, Claus Haslauer
<ch******@scimail.uwaterloo.ca> wrote:

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

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.price) AS MaxOfPrice SELECT tbl_Basis.mydate
FROM tbl_Basis GROUP BY tbl_Basis.mydate 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.
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

Hi
NO NO NO to my previous post ! Think twice, post once.
See the corrected SQL above

David