Thorny problem graphing XAxis dates

Sorry, I'm not really sure how to state this question succinctly. I
have data in an MSAccess Db re medications and the time\date and
amounts taken. Mads are entered only in the db on days when they are
taken yet I need the graph to show ALL days including days not taken.
That is, days when the amount is inferred to be zero because no data
appears for that day. Sorry, that's probably as clear as mud.

Currently, I'm using VBA code to select the data, build a spreadsheet
and then build a graph from the spreadsheet I built. The problem is
that it (obviously) graphs only the days meds are taken, not days in
between.

I did something like this many years ago using brute force coding but
it is a torturous coding process and doesn't seem very elegant. I
think I may have seen a way to do a SQL select statement that
accomplishes what I want but my thinker is getting old and sometimes
unreliable.

Does anyone have any ideas on this?

Thank you,

Don in Redmond

Don -

You need to make sure your dates are recognized by Excel as numerical
values, not as text labels. Coming from Access, it's not unlikely that
they'd be imported as text. You can use Text To Columns to try to fix a
nonnumeric column of dates.

Then you need to make sure these dates are used as the category values.
It sounds like this is not an issue in your case.

Finally, you need to make sure that your chart has a time-scale category
axis. If the dates are recognized as numeric, Excel does this
automatically, which is why I started with my first paragraph. Fix the
dates, and the axis should take care of itself. (You'll have to
specifically change the axis of an existing chart that was first made
with text labels.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Don wrote:
> Sorry, I'm not really sure how to state this question succinctly. I
> have data in an MSAccess Db re medications and the time\date and
> amounts taken. Mads are entered only in the db on days when they are
> taken yet I need the graph to show ALL days including days not taken.
> That is, days when the amount is inferred to be zero because no data
> appears for that day. Sorry, that's probably as clear as mud.
>
> Currently, I'm using VBA code to select the data, build a spreadsheet
> and then build a graph from the spreadsheet I built. The problem is
> that it (obviously) graphs only the days meds are taken, not days in
> between.
>
> I did something like this many years ago using brute force coding but
> it is a torturous coding process and doesn't seem very elegant. I
> think I may have seen a way to do a SQL select statement that
> accomplishes what I want but my thinker is getting old and sometimes
> unreliable.
>
> Does anyone have any ideas on this?
>
> Thank you,
>
> Don in Redmond

If I understand you correctly, after you have created the chart, select
it, then select Chart | Chart Options... | Axes tab. In there make sure
the x-axis type is 'Timescale'.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <[email protected]>,
[email protected] says...
>
> Sorry, I'm not really sure how to state this question succinctly. I
> have data in an MSAccess Db re medications and the time\date and
> amounts taken. Mads are entered only in the db on days when they are
> taken yet I need the graph to show ALL days including days not taken.
> That is, days when the amount is inferred to be zero because no data
> appears for that day. Sorry, that's probably as clear as mud.
>
> Currently, I'm using VBA code to select the data, build a spreadsheet
> and then build a graph from the spreadsheet I built. The problem is
> that it (obviously) graphs only the days meds are taken, not days in
> between.
>
> I did something like this many years ago using brute force coding but
> it is a torturous coding process and doesn't seem very elegant. I
> think I may have seen a way to do a SQL select statement that
> accomplishes what I want but my thinker is getting old and sometimes
> unreliable.
>
> Does anyone have any ideas on this?
>
> Thank you,
>
> Don in Redmond
>

>you need to make sure that your chart has a time-scale category
>axis.

Thanks Jon, it's exactly what I need. My data was of the correct data
type, but I did not select 'Time Scale' for the XAxis.

Regards,

Don

On Wed, 21 Sep 2005 07:52:26 -0400, Jon Peltier
<[email protected]> wrote:

>Don -
>
>You need to make sure your dates are recognized by Excel as numerical
>values, not as text labels. Coming from Access, it's not unlikely that
>they'd be imported as text. You can use Text To Columns to try to fix a
>nonnumeric column of dates.
>
>Then you need to make sure these dates are used as the category values.
>It sounds like this is not an issue in your case.
>
>Finally, you need to make sure that your chart has a time-scale category
>axis. If the dates are recognized as numeric, Excel does this
>automatically, which is why I started with my first paragraph. Fix the
>dates, and the axis should take care of itself. (You'll have to
>specifically change the axis of an existing chart that was first made
>with text labels.)
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>
>Don wrote:
>> Sorry, I'm not really sure how to state this question succinctly. I
>> have data in an MSAccess Db re medications and the time\date and
>> amounts taken. Mads are entered only in the db on days when they are
>> taken yet I need the graph to show ALL days including days not taken.
>> That is, days when the amount is inferred to be zero because no data
>> appears for that day. Sorry, that's probably as clear as mud.
>>
>> Currently, I'm using VBA code to select the data, build a spreadsheet
>> and then build a graph from the spreadsheet I built. The problem is
>> that it (obviously) graphs only the days meds are taken, not days in
>> between.
>>
>> I did something like this many years ago using brute force coding but
>> it is a torturous coding process and doesn't seem very elegant. I
>> think I may have seen a way to do a SQL select statement that
>> accomplishes what I want but my thinker is getting old and sometimes
>> unreliable.
>>
>> Does anyone have any ideas on this?
>>
>> Thank you,
>>
>> Don in Redmond

Thanks Tushar, you understand me perfectly correctly and your
suggestion is right on.

Thank you,

Don


On Wed, 21 Sep 2005 18:56:32 -0400, Tushar Mehta
<tm_200310@tushar_hyphen_mehta_dot_see_oh_em> wrote:

>If I understand you correctly, after you have created the chart, select
>it, then select Chart | Chart Options... | Axes tab. In there make sure
>the x-axis type is 'Timescale'.