MS Graph: positioning items on x-axis

[Neil]

I have a situation using MS Graph (chart object) within Access that I'm not
able to resolve.

I have events that happen at various times during the day. Time is the
x-axis; event value is the y-axis. The chart covers a 24-hour period, from
midnight to midnight.

I want the events on the chart to fall on the graph where they fall during
the day. For example, if something happens at 6 am, I want it to appear 25%
in from the y-axis. Noon should appear halfway between the two, etc.

What's happening instead is that my events are evenly distributed between
the two ends of the x-axis.

So, say, for example, I have three events on a day: 6 am, 7 am and 3 pm.
These will appear evenly-spaced along the x-axis, so that there's the same
distance between 6 am and 7 am as there is between 7 am and 3 pm. That's not
what I want. I want the events to appear approximately where they would fall
on the 24-hour scale.

Any ideas as to how I can accomplish this?

Thanks!

Neil

 

[Neil]

I should note that I did notice that MS Graph has an option for "time-scale"
for the x-axis, and then, when that's selected, you get to select the min,
max values for the scale, the major unit, etc.

Only thing is: it only seems to support days, months, or years. Doesn't have
hours, unfortunately, which is what I need. So I seem to be out of luck
here.

Anyone have any suggestions for replacement graphing tools within MS Access
that might be more feature-rich?

Thanks!

Neil

 

[JHB]

I have a situation using MS Graph (chart object) within Access that I'm not
able to resolve.

I have events that happen at various times during the day. Time is the
x-axis; event value is the y-axis. The chart covers a 24-hour period, from
midnight to midnight.

I want the events on the chart to fall on the graph where they fall during
the day. For example, if something happens at 6 am, I want it to appear 25%
in from the y-axis. Noon should appear halfway between the two, etc.

What's happening instead is that my events are evenly distributed between
the two ends of the x-axis.

So, say, for example, I have three events on a day: 6 am, 7 am and 3 pm.
These will appear evenly-spaced along the x-axis, so that there's the same
distance between 6 am and 7 am as there is between 7 am and 3 pm. That's not
what I want. I want the events to appear approximately where they would fall
on the 24-hour scale.

Any ideas as to how I can accomplish this?

Thanks!

Neil

Hello Neil.

One way to do it is, to make a (dummy) table with 24 hours in and left join the two tables on the time-field.

Depending on how exactly it should be, it can be inserted half and quarter hours or minutes of each hour.



Regards

Jørn

 

[Rob Parker]

Sound like you've got the wrong chart type - probably linear. You need to
select XY as the chart type in order for the x-axis values to plot correctly
on the x-axis. You will be able to set the max and min values for this
scale - they will be 0 and 1 to cover a 24-hour period (since the time
portion of a date/time field is stored as the decimal proportion of 24
hours). The only oddity is that the 24:00 value will display as 0:00 (since
it's actually midnight of the next day) - I don't have a work-around for
this, sorry.

HTH,

Rob

 

[Neil]

Thanks, yeah, I was using a vertical bar chart. Didn't realize you'd get
different options with different chart types. I tried the XY scatter chart
and it worked fine, gave me the options I needed. I'll show it to the client
and see if they're happy with that type of chart instead of the bar chart,
but I think they will be. Thanks again!

Neil

 

[Neil]

Wait, one thing I just realized is that I was using a stacked vertical bar
chart, which gave the total for two values by stacking them on top of each
other. That's not there with the XY scatter chart.

We have two devices, each has a run time. I am charting the run time for
each on the chart. Sometimes one will run alone; sometimes the two will run
together. When the two run together, it's good to see the individual run
times, as well as the total run time.

With the stacked bar chart, the two run times were stacked on top of each
other, showing the total time at the top of each bar. With the scatter plot
there is no total.

I could, of course, manually create a third set of data for the total, and
plot that. Problem is, when there is only one device running, the total time
and the single device time are the same, so the device time plot point gets
hidden, and so you don't know which device it is.

The stacked bar chart worked well in that you could see each device's time,
as well as the total for both. But I don't have the x-axis options with that
that I have with the XY chart (which I find strange, to be honest).

Anyway, any ideas? Thanks!

Neil

 

[Rob Parker]

Hi Neil,

Since these been no other takers for this (probably because there's no
simple answer), here's my thoughts.

There is no easy way to do what you want - the MS Graph package doesn't let
you combine any of the bar/column chart types with a scatter chart. If you
need that, you'll have to fudge it.

I suggest that the easiest way will be to set up a query to use af the
chart's recordsource, which has a full set of the time values you want for
the x-axis and the 0, 1 or 2 y-values for your bars. The key to this will
be a table containing all the time values you want, at the granularity you
require (eg. 1 hour, 1/2 hour, 15 minute, ... (and I'd suggest that less
than 15 minute will become awfully cluttered)). Join this table to the time
field in your current query, click on the join line and in the Join
Properties dialog select "Show ALL records from [timetable] and only those
....".

If your incoming data is at random times, I think the easiest approach would
be a preliminary query to round the time to the granularity that you've
chosen to use.

HTH,

Rob

 

[Neil]

Well, what I ended up doing is just including both graphs on each report
page. The bar chart easily shows the combined values and is much more
readable. The scatter graph shows where the items fall relative to the
24-hour scale, but is less helpful value-wise. So having both charts on the
page is good. Each has their own purpose. The client is happy with that
approach, so that works for me.

Hi Neil,

Since these been no other takers for this (probably because there's no
simple answer), here's my thoughts.

There is no easy way to do what you want - the MS Graph package doesn't
let you combine any of the bar/column chart types with a scatter chart.
If you need that, you'll have to fudge it.

I suggest that the easiest way will be to set up a query to use af the
chart's recordsource, which has a full set of the time values you want for
the x-axis and the 0, 1 or 2 y-values for your bars. The key to this will
be a table containing all the time values you want, at the granularity you
require (eg. 1 hour, 1/2 hour, 15 minute, ... (and I'd suggest that less
than 15 minute will become awfully cluttered)). Join this table to the
time field in your current query, click on the join line and in the Join
Properties dialog select "Show ALL records from [timetable] and only those
...".

If your incoming data is at random times, I think the easiest approach
would be a preliminary query to round the time to the granularity that
you've chosen to use.

HTH,

Rob

Wait, one thing I just realized is that I was using a stacked vertical
bar
chart, which gave the total for two values by stacking them on top of
each
other. That's not there with the XY scatter chart.

We have two devices, each has a run time. I am charting the run time for
each on the chart. Sometimes one will run alone; sometimes the two will
run
together. When the two run together, it's good to see the individual run
times, as well as the total run time.

With the stacked bar chart, the two run times were stacked on top of each
other, showing the total time at the top of each bar. With the scatter
plot
there is no total.

I could, of course, manually create a third set of data for the total,
and
plot that. Problem is, when there is only one device running, the total
time
and the single device time are the same, so the device time plot point
gets
hidden, and so you don't know which device it is.

The stacked bar chart worked well in that you could see each device's
time,
as well as the total for both. But I don't have the x-axis options with
that
that I have with the XY chart (which I find strange, to be honest).

Anyway, any ideas? Thanks!

Neil


message