Inserting Chart in Microsoft Access

[BittenApple]

Hello team,

I need to insert a chart based off of the query. Basically, I think Microsoft Access has some limitations on charts; I have 10 fields whose data type is numeric and two fields for the name of companies and agents; in fact I have 3 data points; I thought for the name of the agent, I can do a parameter query and the next two data points can be shown on the chart.

I have watched video trainings and so on. It seems that I can't have more than 6 fields to be able to insert a chart in Access form or Report, or another option is that I can really have more than 6 fields, but I have to take a different direction and that is to insert an unbound object and start off from there.

Is there a way around it? What is the best approach? I want the chart/ charts to be eventually to be displayed as reports; so having them on form is all right, but at the end, end users should be able to see them as reports.

Any tip is greatly appreciated.

Regards,
BitBit

[HansV]

The chart wizard is very limited, but to get around that, create a very simple chart, then change the Row Source property of the chart control to your query.

[BittenApple]

Hello Hans,
Hans:
create a very simple chart, then change the Row Source property of the chart control to your query
Me:
Should I create the small chart with the Wizard first?
My approach
I did, I created a small chart with the wizard and then I changed the query for row source of the chart. Chart doesn't look all right and I have to change it, one of the reasons that the chart doesn't look all right is that I have many data points. I have agents, companies and 10 fields with numeric data types. I can do easily in Excel, but I am not sure I can do same in Access.

I'd like to get the chart on the report rather than on form to make end user job easier.

Any tips,
Regards,
BitBit

[HansV]

Create a query that returns only the data that you want to display in the chart. Leave fields that you don't want to show in the chart out of the query.

[BittenApple]

Hello Hans,

Access is not friendly with charts. I can't get the same view which I take normally in Excel.
I want company and agent to be on X axis, and all metrics becomes my data series, I am only able to get either company or agent in the chart. I can't get both at same time.
I tried different things as:
Category X as company name and second Y axis for agents and the rest of 10 fields for values. I don't know how to insert second axis in Access (perhaps this is not a correct approach, it works in Excel if I am not wrong but not in Access)
I tried cross tab queries and that requires only 1 field, I have 10 fields to show for this chart for 5 agencies and 6 hospitals.
I tried a month helper table, it didn't work.
I tried to do a parameter query to ask for a hospital name to show the chart for all agencies for those 10 fields whose data type are numbers and this approach worked.
Is there a better approach?

Regards,
BitBit

[HansV]

I agree that Excel is much easier to work with. You might consider creating a chart in Excel based on data in your Access database.

[BittenApple]

Hello Hans,
Thanks for the response.
How to make the charts in Excel to be available to all those who run the report? I'd like to insert charts on the report.
Regards,
BitBit

[HansV]

That is not really possible. You'd have to tell the users to view the chart in Excel.

[HansV]

If you want a chart in a report, you'll have to create the chart in Access itself.
It's best to create it on a form first, because a form is more interactive.
If you view the form in form view, you can double-click the chart to edit it. You can then assign series to a secondary axis, etc.

S134.png

[BittenApple]

Hans,
Thank you so much. You are referring to good points.
I worked a lot on it today. I was creating the chart on report and it was very disappointing, I was able to bring only one field out of my 10 fields on the chart. Eventually, I thought Microsoft can't work like this. I went to property sheet and I added the column counts to 10. It has become a way better, still, I have to work on it. With your suggestion to create it on the form, I might come closer to what I want.
I have Month, hospitals and 10 fields, I have to show all in one chart.
Regards,
BitBit