Link to home
Start Free TrialLog in
Avatar of Susan Stevenson
Susan StevensonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS Access Chart..

Hi,

I am trying to create a column chart which has 2 axis the 2nd of which (right) will be driven by an underlying field in my query.  Is this possible?

I have got the basic chart working as attached but the data also contains another field called SLA which I want to portray as a line on the same chart.  I don't think I want trendline as from what I understand this is just a floating variation of what is already on there (I might be wrong)

Thank you in advance..
Susna
RptAnsweredVsAbBYHour.pdf
Avatar of Susan Stevenson
Susan Stevenson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I am doing this the reports in access by the way.
Avatar of Jeffrey Coachman
What version of Access please?

<I am trying to create a column chart which has 2 axis the 2nd of which (right) will be driven by an underlying field in my query.>
Then you most likely will need to use a Group By, or crosstab query as the source, ...not a standard Select query

How did you create your existing chart?
The chart wizard in Access can create the entire chart you are describing quite easily.

In any event, it is much easier to help if you post a sample of the db itself, as the exact solution will depend on what you have already.

But again, give the chart wizard a try and see how you get on:

Open a blank report in design view.
Click the "Insert Chart" icon.
Then follow the rest of the wizards steps.
Drag in both Offered and Abandoned on the left, and put the time values on the bottom (Grouped by the "Hour")
You may have to go back and format the chart's rowsource to something like this to show military time:
SELECT (Format([TimeValue],"Short Time")) AS Expr1, Sum(YourTable.Offered) AS SumOfOffered, Sum(YourTable.Abandoned) AS SumOfAbandoned
FROM YourTable
GROUP BY (Format([TimeValue],"Short Time")), (Int([TimeValue]*24));


FWIW, here is a sample db of how I visualize this.
So you can play around with this as well and try to get it working in your database.

;-)

JeffCoachman
Database37.mdb
screenshot:User generated image
Thank you but I have already got to the point that you have outlined in your database. What What I need to do is:

Add a field called SLA (%age) to the table (formula = Answered / Offered)

Add an axis on the right of the graph 0 - 100% and have this plot in a line on the same graph the SLA per half hour slot.  

I hope this is clearer and thank you for your response.. awaiting another one eagerly!
Sorry ... it is 2007..
I am so sorry - I am multitasking here so I have missed a couple of your questions.  I did use the wizard to get what I had so far and it is working as it should.  In theory the only things I need to do are above but I don't think they are as easy as they seem.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Susie1980,

You are in good hands; I just wanted to react to this comment: “I did use the wizard to get what I had so far...”. The Chart wizard is one I abandoned almost immediately, and didn't even bother showing when teaching Access. The fact is that this is an impossible wizard to write — there are just too many ways one can consolidate data before charting it.

The best solution is to create a chart (just click [OK] as soon as you are allowed) and then construct the query as you need to. For example, given your data you don't need a total query, as the rows are already consolidated by half hour... When you have the data you need, copy it, open the chart in design view, and paste the data to the datasheet.

Cheers!
(°v°)
harfang is correct.
(Bolting out of the chart wizard ASAP is something I learned from him.)

A lot of people wonder why Charts in Access are so frustrating.

Part of the reason is that Access presumes that you want to summarize your data.
(Where as Excel just plots what you select.)
This causes more confusion, because some people feed Access Summarized data to begin with. ("This query summarizes sales by Country, now I need a chart")
The other frustrating thing is that "editing" the charts in Access is not as straightforward as it is in Excel.

The chart "Placeholder" in design view (that looks nothing like your actual chart) has baffled me ever since I made my first chart.
;-)

Finally VBA in charts is something even I dread, so moving to code based solutions will offer no relief...

Sometimes you need a PK, sometimes not, ...sometimes you can use a select query, other times you need a Group By or even a crosstab query.

If it is any consolation, ...sometimes I even get twisted in Knots.

It's just one of those things you have to play around with enough to get comfortable with.

I'm sure we could go on all day...

;-)

Jeff
<Off topic>
h,

I think I lost your email...

In any event, can you visit here:
https://www.experts-exchange.com/questions/27781431/export-filtered-record-source-of-a-report.html

If you can find a more efficient approach, please post...
Thank you.  I think I'm getting my head around Access processes it now.  Thank you for your help, it works well.
Thank you all for your comments - Yes I agree now that in future it is just something I'll probably spend hours playing with before I get the chart i want. Quite annoying when it looks so simple!!
Jeff,

For a while I had my EE mail in my profile (as page editor) but that stopped working; I put my regular e-mail back a little while ago. Anyway, I had already spotted that question!

(^v°)