Bubble graph in MS Access 2000 masks or reports - how to link it to table/query?

I am trying to create a bubble graph in an MS Access 2000 form, starting from a query in which I have: category name, X, Y and Z values. I cannot manage to do it.
Can you do it for me, starting from the .xls and .mdb models attaqched? In the Excel file you have exactly the same table and graph. Thanx.
Who is Participating?
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.

Sar1973Author Commented:
Here you got them.
Jeffrey CoachmanMIS LiasonCommented:
1. <Can you do it for me>

This is not a "do it for me" site.
(That would be like you saying, "I can't do my homework, can you do it for me?")

2. A bubble chart needs three values. A "X" co-ordinate, a "Y" co-ordinate (to locate the center of the bubble in two dimensions)
Then it needs "Value", to determine the size of the bubble.
For example:
X                      Y                      Quant
7.841834007      4.594837156      17
8.162582239      3.080054194      84
4.485693447      2.072708249      89
8.104555656      9.129162879      79
7.497657594      5.806943124      34
6.795253057      3.100572967      41
8.299150567      1.826542859      65

3. I am totaly lost as to what the Excel is trying to communicate.
But here are tutorials on how to create Bubble charts in Excel:

4. In the Access data can you clearly state what the X,Y and Value fields are.



X	         Y	         Quant
7.841834007	4.594837156	17
8.162582239	3.080054194	84
4.485693447	2.072708249	89
8.104555656	9.129162879	79
7.497657594	5.806943124	34
6.795253057	3.100572967	41
8.299150567	1.826542859	65

Open in new window

Sar1973Author Commented:
Jeff, my problem is that what I can do with Excel (and I did it, as you can see from the attachement), I cannot with Access. I have tried to create a graph in this way:
1. in "Masks" I create a new mask (Graph) with "Risultati" query;
2. I select "Categoria", T/M (should be X), C/T (should be Y) and R (should be Z);
3. I select bubble chart
4. I select "Categoria" as series, but when I get asked to choose the data I cannot insert the exact combination that allow me to associate X, Y and Z correctly as I do in Excel.
Access only accepts SumOf data and creates time to time a table with this data.
How can I solve this problem, then, If you cannot act on the files I have attached?
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:
Again, if you tell me what the X, Y and Value fields are, this can be done quite easily.

The chart in Excel is based on the percentages.
There are no percentages in Access.
What do you want the Access chart to be based on Percentages or the values in the table?
Sar1973Author Commented:
The data in the 2 files is the same: T/M as X, C/T as Y, R as Z.
If you can explain how to set them in Access graph form/report, I'd be grateful.
I'm having a similar problem so I'll be interested to see how this turns out.  I've created a form called Risultati with a bubble chart on it using your source data.  T/M values on the X Axis, C/T on the Y Axis and R as Z Axis which determines the size of the bubble.  The challenge comes in trying to get the "Categoria" to display in the graph, because you need to be able to know which bubble belongs to what Categoria.  The problem is that if you include "Categoria" into the source query you loose the values from the X Axis.  My only work around is to display a subform with the source data in it so you can more or less work what bubble belongs to which Categoria.  I'm only a novice, so I'm interested to see what the experts say

Jeffrey CoachmanMIS LiasonCommented:

I really don't like "Guessing",

I have requested twice for the Translations.
So, one more time...
Please tell me what values in your table are the equivalent of the X, Y and Values in a standard bubble chart.

I then asked what you wanted you chart based on, the values or the percentages.
You have not answered either of these questions?

Again just tell me the equivalents and we are all set.


Sar1973Author Commented:
Jeff, if you read my last post you'll find the answer, but I suspect that for some reason you didn't.
Anyway, I think that Trevor solved my problem, but I haven't implemented yet his solution in my DB, so you'll have to wait a bit for having the question closed.
Jeffrey CoachmanMIS LiasonCommented:
I apologize, I had not refreshed my browser.

Jeffrey CoachmanMIS LiasonCommented:

Are you straight with the solution trevor posted, or do you still need help?

Jeffrey CoachmanMIS LiasonCommented:

Your concern is really a new question.

You should post it as a new question.
Post the lnk to it here, and I will post my solution.
My solution uses VBA, but there may be other Experts who can do better.

Because the source table is not in the standard format, you can't easily use an indirectly related field as a data lable.
You only have the X value, the Y Value or the "Value" Field name available.
The only way I was able to do this was using VBA

Here is a screenshot.

I know this probably should be a new question, but I think the answer is no anyway, so hence asking you here.  Do you have any suggestions on how to get the categories onto the bubbles while still preserving the X & Y axis as values?

Jeffrey CoachmanMIS LiasonCommented:
Again, I don't know what you mean by "preserving the X & Y axis as values"
The values are still "preserved" in the underlying table/query.

Do you mean "Display"?
Hi Jeff,

As per your recommendation I have posted a new question

Ref : http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_24464784.html

Your comments and proposed solution above with the screenshot are exactly what I'm looking for.


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
Sar1973Author 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.