Solved

Chart / Graph scales - How to fix sensible values on the X axis

Posted on 2004-10-31
1,063 Views
Last Modified: 2008-01-09
My Access runtime application includes plotting a graph from a table where the X axis is a date/time field with a variable time interval. The report can cover anything from a few hours up to several months, and from 2 to 2000+ records, so the AutoScaling feature is essential - and works well.

However, the axis label values that Access selects from the table nearly always look stupid, because say the range is 15/01/2004 15:54 to 18/01/2004 12:08 (which is 4096 minutes), Access will print 18 labels starting at the first record and then every 228 minutes (nearest 18th of 4096),  eg 15/01/2004 15:54, 15/01/2004 19:42, 15/01/2004 23:30 etc. which is hardly easy to follow!  A human would have chosen 4 hour gaps, and then plotted values at 16:00, 20:00, 24:00 etc.

(By the way, the number of labels you get seems to depend only on the size of font selected - the bigger the font, the less labels)

So, what I  need is a way to stipulate the first value label (15/01/2004 16:00 in this example), and the gap (240 mins), whilst still graphing the full set of results as now.

Thanks for any help.



0
Question by:pauldownham
    22 Comments
     
    LVL 4

    Expert Comment

    by:IDAnderson
    pauldownham,

    To ensure that your chart prints the first record on a particular date, make sure that the date field (bound text box) appears somewhere on your form or report (if you do not want the date visible set the text box properties - Visible No).

    To set the x axis, double click on the chart in design view, click on the x axis to select it, right click and select format axis. Go to scale tab and set according to your requirement.

    best regards,

    Ian..
    0
     
    LVL 58

    Expert Comment

    by:harfang
    I'm assuming you use MS-Chart to create the chart.
    Make sure that MS-Chart knows that you are working with a time scale (under "Chart / Options")
    Try changing some of the scaling options to see if that gives you the result you want.

    Hint: copy some actual data from the source query to MS-Chart, so that it makes sense... :)

    If you need to change the various options by program, it is useful to know that the data model of MS-Chart is largely (or completely?) compatible with Excel. You can thus use Excel's macro recorder to learn how to program for Chart as well.

    For example, I just changed dynamically the minimum scale of an embedded chart with:

           Me.OLEUnbound0.Object.Axes(1).MinimumScale = CLng(#1/2/2000#)

    Try also .BaseUnit and .MajorUnit
    Again, it is best to rebuild your chart in Excel, and then record all the changes you need for the scale. Then it will be easy to program that in Access.


    Then you will discover that the logic behind setting a scale factor, a minimal value and a unit is far from trivial...

    Lots of fun!
    0
     

    Author Comment

    by:pauldownham
    IDAnderson,

    Thanks for your reply ... but I don't think I made my question clear enough!

    The chart is printing fine, and covers all the records as intended, its just that the labels on the horizontal axis (time line), whilst perfectly correct, look silly because Access has chosen the values  automatically and I would prefer to specify them at more obvious places. For example, Access takes the first record's date and time as the first label (which is fair enough!), but a human wouldn't normally choose "15/01/2004 15:54" as a good label, nor that the next one should be 228 minutes later. For clarity, I would much prefer "15/01/2004 16:00" as the first label, and the others at 4hr intervals.

    I can't use fixed scaling (because the results to be plotted are so variable), so I was looking for any tips on how to somehow force sensible values as the labels. It may be that I don't let MsGraph work them out at all, but somehow print values derived from calculations on the side?

    0
     

    Author Comment

    by:pauldownham
    Harfang,

    Thanks for your suggestion.

    My application is a runtime Access program intended for later distribution, so I can't use Excel as an alternative to the inbuilt MSGraph, although I appreciate you are mostly suggesting this as a test arena.

    I will look at your code, but presumably this is just the same as fiddling with the options within the setup screen? As per my comment above, I suspect the solution may have to be found as a way to replace the standard values?

    Cheers
    0
     
    LVL 4

    Expert Comment

    by:IDAnderson
    Pauldownham,

    Sorry for the misunderstanding.

    If you are wanting to round the data to, say, nearest 4 hours you could add calculated fields to your base query so that they calculate the data between defined intervals (if the time is > 20:00 and < 24:00 sum the total number of labels printed).

    Please advise if I am on the right track. If you advise that I am on the right track I will begin to tinker with the code to determine the values in the calculated field.

    Needless to say, the calculated fields would provide the base data for your chart.

    regards,

    Ian..
    0
     

    Author Comment

    by:pauldownham
    Ian,

    I'm not sure if you are on the right track!

    The example I've given is just an example. As per the original question, the graph is plotting results which can be anything from 2 records to 2000+, and can span a short time (in which case the labels may only be minutes apart), or up to a year (in which case they may be weeks apart). As a human labelling up the graph, you could always choose sensible labels to suit each occasion, even where some of these may not be data points:-

    As another example, say the data points are (and I'll only do the times for clarity):

    23:57, 00:03, 00:09; 00:15 .... (and then another 100 at 6 minute intervals).

    A human would probably choose labels at half hour intervals (=20 labels), and start the axis labelling at 00:00. The first point plotted would still be 23:57, and all 104 results would still be plotted, but there would be an easy to follow timescale (00:00, 00:30, 01:00 ... ) printed along the axis.

    With Access doing the labelling, you would likely get:

    23:57, 00:32, 01:07, etc. (start at first, and then 35min gaps (18th of 624)).

    which is an impossible timescale to follow!




           
    0
     
    LVL 58

    Expert Comment

    by:harfang
    I made a few tests:

    With MS-Chart I never got quite that behavior. The value 240 minutes is possible (4 hours, 1/6th of a day), but in that case the labels are also rounded to the next 6th of a day (i.e. 0:00, 4:00, 8:00, etc.). I found no way to have the chart start with a label of 12:54, for instance. How did you do it?

    Once in Chart, what are the options in "Chart / Chart Options", tab "Axes"?
    What is the base chart type?
    When you double-click the time-axis, tab "Scale", are there any options NOT set to Auto?

    It should really work better. We might have to resort to VB coding, but I hope not!

    Cheers!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Chart can compute "time-scales" only for line charts and XY (scatter) charts. If you have a bar chart, the time is never actually read, it is directly used for the bar labels.

    Is your DATA actually always 18 points at regular intervals???
    0
     

    Author Comment

    by:pauldownham
    Harfang,

    You may find the comments to Ian useful.

    I'm using MSGraph (is that the same as MSChart?), and have the following X axis options:

    Patterns Tab:
    Lines = automatic
    major tick = outside
    minor tick = none
    tick mark = next to axis

    Scale Tab:
    Value Y axis crosses = 1
    Number of categories between tick mark labels = 10
    number of categories between tick marks = 100
    (all other boxes left unticked)

    Font Tab:
    Arial, Regular, 5.75
    Autoscale = ticked

    Number Tab:
    custom, dd/mm/yyyy hh:mm

    Alignment Tab:
    0 degrees, context
    Offset = 100

    0
     
    LVL 58

    Expert Comment

    by:harfang
    Ok, we have an answer...

    When you write "Number of categories" as any option of the scale tab, it means that you have categories... :) In other words, MSGraph never computes anything. It will simply take the date/time value you provide and stick it below the value.

    If you use a line-chart, you should really check "Chart / Chart Options" and try to select "time-scale". The chart will be more accurate and you will have a true axis scale management.

    If you use a bar-chart or the like, it's entirely different and it might become very complex in a very short time... But we're up to it!

    Cheers!
    0
     

    Author Comment

    by:pauldownham
    Harfang,

    Interesting!

    My graph is a simple line chart, and I've tried your suggestion of selecting time-scale rather than category ... and hey presto ... sensible labels. BUT, (thats a very big "but"), the graph itself has gone completely stupid!!

    It seems the smallest unit of time selectable is Days, so my nicely curving chart with plots every 6 minutes has become a series of straight lines with all the results bunched together into days.

    I think if we can change this to minutes we would be there for the example data ... except of course this selection needs to be automatic depending on the time spread of the records in each case.

    Thanks

    Paul
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Ok, then go with "XY (scatter)". It will look exactly like Line chart if your data is sorted, but without the "base unit" feature. It will display correctly even seconds.
    The downside is that it has no "month" and "year" tick marks, always an integer number of days, so that the time axis over wide time spans will again become less elegant.

    Glad I could help.
    0
     

    Author Comment

    by:pauldownham
    Harfang,

    We're getting close!

    The best looking graph is "XY scatter data points connected by lines", and the date/time labels are looking good - but still a problem with the X axis!

    There are big unplotted regions at the left and right of the plot area because the system has chosen dates to start and finish on that are outside the range of the records. Looking inside the axis properties, all the Auto boxes are ticked, but the values are strange - eg 38001.6 as the minimum (I would have expected a date/time), and on the Chart Options/Axes all the options are greyed out with X set on automatic (rather than time-scale where we left it!).

    Thanks for your help
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Yes, I can explain this.

    The XY scatter needs two numerical values, x and y, for each point. Thus, everything happens just as if the function CLng was used on the data.
    From Access, open the Immediate Pane / Debug Window and try the following:
    ? CLng(Date)
    ? CLng(# 1 apr 4:12 #)
    and of course
    ? CDate(38001.6)
    to understand the reading.

    The last major improvement of MS-Graph (an of graphics in Excel) dates back to the version 5. At that time, there was an important demand for financial graphs, so that the automatic treatment of dates for Line Charts has been added. Alas, the scientists never put enough pressure on MS to get some of the most trivial enhancements... This is why we are left with this largely unuseable system.
    (end of flames)

    Access will choose the previous Major Unit mark for the leftmost date/time and the next Major Unit mark for the rightmost. This allows the labels to correspond to something useable and to fall on logical times (e.g. noon).

    You can force narrower margins by forcing a smaller major unit. If you need that, you can use some VB instruction like in my very first comment to adjust only that.

    I would suggest dragging the borders of the plot areay so that it fills the maximal available space and leave it at that, but it's your call.

    Tell me if you need more help.
    0
     

    Author Comment

    by:pauldownham
    Harfang,

    This is looking good  ... just need some ideas on code now I think.

    I've experimented with the CLng function, and can produce a perfect graph by unticking the auto boxes in the X Axis panel and typing in my own worked out numerical values. I reckon I can also work out these values for any set of data (and make them part of the data), so what I need next is some wonderful code that writes the calculated numbers into the X Axis boxes at each time of plotting.

    As an overview, this graph is part of a runtime package for distribution to people whose printers I won't know about, so I'm already sacrificing vast areas of my page to huge borders so that it will always fit their paper settings - so to loose yet more to blank sections within the plot area is not an attractive option.

    Thanks again
    0
     
    LVL 58

    Accepted Solution

    by:
    For a MS-Graph chart called uofChart on the detail section, you can use...

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

        With uofChart.Object.Axes(1)
       
            .MinimumScale = CDbl(#1/1/2004 12:00:00 PM#)
            .MaximumScale = CDbl(#1/2/2004 6:00:00 PM#)
            .MajorUnit = CDbl(#4:48:00 AM#)   ' or 1/5
            .TickLabels.NumberFormat = "d h:mm"
           
        End With

    End Sub


    Of course, the values can be calculated elswhere, or even on the report itself...

    Have Fun!
    0
     

    Author Comment

    by:pauldownham
    Sorry, I'm being thick here!

    Where do you find out what the chart is called (your example is uofChart), is it the Unbound Object Frame name (from the pop up properties you see when right clicking the chart)?

    Also, where does the code go ... I'm only used to entering these as Event Procedures on forms?

    Thanks again

    PS Your 500 points are nearly ready!!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    The code is a "Private Sub Detail_Print", or the Print event of the Detail section. So it is just another event procedure... If you chart is on another section, use that section's print event.
    And yes "uofChart" is the name of the "unbound object frame" containing the MS-Graph object, you got that right :)

    Good Luck
    0
     

    Author Comment

    by:pauldownham
    Harfang,

    This technique seems to do the job ... so I'm now working on some fancy formulea to get nice looking labels and gaps for all the likely cases that might arise in our data.

    Its good to be in control!

    Thanks for your help, and the points are awarded.  

    (You only got a GOOD, because I'm otherwise giving out too many EXCELLENTS!)

    Cheers


    Paul
    0
     
    LVL 58

    Expert Comment

    by:harfang
    > You only got a GOOD

    I consider that rude :)

    I am used to getting only EXCELLENTS, this HURTS! :)

    Good luck with your charting...
    0
     

    Author Comment

    by:pauldownham
    Harfang,

    I wouldn't want you to feel hurt, but I don't think I can change grades after the event!

    So, here's a secret plan:

    There's just one more thing to resolve on this issue - which is a separate question I'm going to post in a few minutes - and if you get that answered straightaway, not only will you get more points, but also an excellent!



    0
     
    LVL 58

    Expert Comment

    by:harfang
    :)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now