MS Access Chart Formatting

Posted on 2012-08-28
Last Modified: 2012-09-06
In the sample database I have Sample Report which is the graph I want to modify, the graph data is a crosstab query based on the Sample Query.

What I would like to do is add a horizontal line in the graph to plot the flat Facility Avg Index, on the line I want to have the text “Facility Average 2.02, Total Equipment Count 253”, and the Facility Average and Total Equipment Counts should not appear in the chart legend.  I’d also like to allow the Y axis scale to auto scale the max value based on the Area Avg Index, currently it scales on the largest value of Area EQ Count, but these values I only want to show in the legend not in the actual graph.  What I did to hide the column in the chart was to set its board and back color to white.

I’ve read a few tricks to do these things in excel but I need to do them in Access 2007.  Any help is appreciated.

Thanks in advance,
Question by:phonepagerfax
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Lets keep it simple...
    Forget complex descriptions...
    Just post a detailed graphic of the exact output you need...

    <I’d also like to allow the Y axis scale to auto scale the max value based on the Area Avg Index, currently it scales on the largest value of Area EQ Count,>
    This cannot (AFAIK) be done, unless you  use the "averages" as the values on which the chart is based...
    ...but lets keep this to one question per post, (as per the rules of this site) and just focus this thread on the "Line" you want...
    Sound fair?


    Author Comment


    Here is a sample chart.


    LVL 74

    Accepted Solution

    Unfortunately, I could not replicate your chart fully because the Rowsource could not execute:

    But in a nutshell, you need to add the "total Average" (2.02) to the charts Rowsource.
    See my attached sample.

    Note that there is no easy way to add a series to the rowsource (Area EQ Count) and not have it appear as a series on the graph.
    It is alternatively tricky to "remove" series from the graph, but still have it appear on the data table.

    So you will have to take what I did here to get the "Average For All", and adapt it to your chart to get the final output you are looking for here.

    Author Comment

    Thank you all for your help.

    Jeff – from now on I’ll make sure to have only one question per post…

    boag2000 – although, your example did not create the chart I was looking for it was very helpful in designing my final solution.

    In the end I created a temp table to hold the values I needed to graph, I removed the overall average from a line to just another series in the column chart.  One promising feature I found in Excel that may come in handy in the future is the ability to change the chart type for a series so, a column chart can plot a single or multiple series in that chart as a line chart type…



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now