Access bar chart showing percentage and amount for two periods

Can an Access bar chart show a number for the amount of sales over (1) the last 4 weeks and (2) the last year, at the top of columns which show the same amount as a percentage of the target figure? (My table has a field for the amount of each sale and another field for the date. The target is a set yearly amount)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
You can do anything you want...

How much trouble you want to go through to do it is quite another story.

How good are you with Chart Manipulation, VBA, and SQL?
Jeffrey CoachmanMIS LiasonCommented:
But to answer your question...
For me at least, this would qualify as more of a custom "Project, rather than a simple question requiring a single straightforward answer.
AlanBornatAuthor Commented:
I'm very good with VBA and SQL but I know nothing of chart manipulation
Perhaps you could point me in the right direction?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
Before chart manipulation, you should first create a query that gives you all the data statistics you need:
<amount of sales over (1) the last 4 weeks and (2) the last year, at the top of columns which show the same amount as a percentage of the target figure...>

...then we can go from there.

But remember without any context here, it is hard for me to determine what, precisely, needs to be done.
Remember, Access charts are designed to display raw or summarized data.
They cant be *easilly* made to display:
"a number for the amount of sales over (1) the last 4 weeks and (2) the last year, at the top of columns which show the same amount as a percentage of the target figure?"
...whatever this means in your context...

So doing this may be more of a "Custom Solution", than a simple question requiring a single straightforward answer, ...and as such, may be beyond the scope of a forum like this.

So it would be helpful if you posted a sample DB.

Sample database notes:
Back up your database(s).
Combine the front and back ends into one database file.
Remove any startup options, unless they are relevant to the issue.
Delete any objects that do not relate directly to the issue.
Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
Compile the code. (From the database window, click: Debug-->Compile)
Run the compact/Repair utility.
Remove any Passwords and/or security.
Post explicit steps to replicate the issue.
Test the database before posting.

In other words, a database that we can easily open and "immediately" see the issue

Then include some type of graphic, displaying the exact output you are expecting based on the data in the sample db.


AlanBornatAuthor Commented:
Thanks for the kind offer!!
I have attached (1) a database with a table and a query (2) an excel sheet showing the chart as I would like it (except that i am not very good at Exeel or charts and I would have preferred the origin to be 0% and the amount to be on top of the columns). I can't get Access to show the amount.
Jeffrey CoachmanMIS LiasonCommented:
What do the 400000 and the 13 represent in your Union query formula?

Also your percents in your query are actually large numbers with a % symbol concatenated on (Text)
(For example: 125% is actually .125, not 125 and a Percent symbol)
So this makes it tricky to use these as "values" in the Y Axis

Finally, it makes me a little nervous that there are values in the query that are "HardCoded"
This means that whenever you need another month, a "User" must manually open up the query and change the hardcoded SQL...

Jeffrey CoachmanMIS LiasonCommented:
...In any event, here is the same graph recreated in Access.
I myself had to use functions and "Hardcoded" VBA constants, just to see if I could recreate the chart in it's entirety.
(Note the SQL for the chart)

We still need to talk about a form based interface to enter the Targets and any other parameters


AlanBornatAuthor Commented:
Hi Jeff

Don't worry about the form - I can manage that - or the 'values in the query that are "HardCoded"' - I only put them in as an example.

I see what you have done, you have put the value in with the label. I assume there is no way of putting the value, separate from the label, at the top of the column?

I only ask this becasue I might want to use colours to indicate 'year to date' or 'last 4 weeks' - for example if i wanted to do a chart showing ytd and L4W for each of the workers (Can't remember how to do this - i am useless with charts!)
AlanBornatAuthor Commented:
i've done a chart to show what I meant in the last comment. Please ignore the fact that the percentages are all wrong - the data is just an illustration - can I put the amounts in as column headers?
Jeffrey CoachmanMIS LiasonCommented:
<can I put the amounts in as column headers?>

Do you mean, On top of the bar?
...Or the column headings of the Rowsource query?
If you mean, On top of the bar, then yes.

Open the report in design view
Double click the chart
Right click any column in the series
Select: Format Data Series
Click the "Data Labels" Tab
Check the box for: Value
AlanBornatAuthor Commented:
thanks, I had already tried that. the only problem is that I don't want it to show the value the column is displaying graphically, I want it to show a different value - the amount rather than the percent - like you did on the other chart, but without the series name (which takes up too much space)
Jeffrey CoachmanMIS LiasonCommented:

Please post a graphic of the *Exact* output you are expecting based on the sample data in the most recent DB you posted...
AlanBornatAuthor Commented:
Here's my wish list. The amounts are all taken from the queries that I sent you
Jeffrey CoachmanMIS LiasonCommented:
Here is the SQL of the chart you posted:
SELECT BarChartQuery2_Crosstab.WorkerID, BarChartQuery2_Crosstab.[Last 4 weeks], BarChartQuery2_Crosstab.[Year so far] FROM BarChartQuery2_Crosstab GROUP BY BarChartQuery2_Crosstab.WorkerID, BarChartQuery2_Crosstab.[Last 4 weeks], BarChartQuery2_Crosstab.[Year so far];

Here is the result of that SQL:
WorkerID      Last 4 weeks      Year so far
1      32      12
6            14
12      4      10
13      39      25
22      1      15
34      40      14

As you can see there are no values here that match the "Values" you want shown in your PDF, hence it is near impossible to make these values appear in the chart, (if they do not exist in the Rowsource.)
Make sense?

In other words, the issue here is that (for the chart you are requesting) the Percents ARE the Values.

So, can this be done?
Sure, but the level of coding and/or chart manipulation required would be beyond what I would be prepaied to do as a Volunteer here.

Finally, remember that the sample I posted matches the ouput you requested in the original Excel chart you posted.
What you are asking for now, (although it appears simple), is quite tricky to do...


AlanBornatAuthor Commented:
Thanks. I could easily add the valus to the SQL, but I assume you are saying it is more complicated than that?
Jeffrey CoachmanMIS LiasonCommented:
You can add the values to the SQL, but then this creates issues with Graph itself, because Access will automatically "Add" this data into the chart, thus changing the layout of the chart away from what you wanted.

And again, the label that appears on top of the bars, gets it value from the "Value" in the SQL.
Again, in your case the Values are the Percentages.
AlanBornatAuthor Commented:
I realise it's the  <Access will automatically "Add"> bit that's the problem. How do you get to stop this, i.e how do you get into the design of the chart? i can do this with all the other Access objects but not charts
Jeffrey CoachmanMIS LiasonCommented:

What you are asking for here is a bit "Custom"

You can't simply "Stop" access form doing what it is designed to do...

You can "Get into the chart" by using VBA and learning the MS Graph Object Model.

What must be done (as far as I can tell) is that you need to set the Label Object's value to the "Amounts" not the Percents.

So you would have to loop the series and lookup the Value, then set that as the caption.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlanBornatAuthor Commented:
This problem is apparently too difficult for this forum
Jeffrey CoachmanMIS LiasonCommented:
Not too difficult, ...just "beyond the scope of"...

I have done things like this before, but it ended up being highly customized to the specif data.

So that being said, there may be an Expert here (or even in another forum) willing to take the time to walk you through this...


AlanBornatAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.