ms/access charts

I have a chart on a report and I want to plot a value against the year. The source is a query containing the year "ano" column and the data (actually 3 series of data on columns labelled "saldoInq" "saldoCapitacao" and "saldoquilibrio" and I want to produce the charts, one per "cod";
I am using the wizard and it previews as expected (well ... most of the time; as it some times is totally blank(!)). The Report is named "SustenEconFin" and I am uploading the database.
can anyone help?
João serras-pereiraAsked:
Who is Participating?
You are right.
I copied and pasted charts. The one in Code footer, uses Code as both Link Master Fields and Link Child fields.

For the total chart, no links, as you did.

Database attached. Bothe reports work.

"What did you do???"  Added the cod field to the report within cod header.
Any further query is welcome!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I am using the wizard and it previews as expected
So what exactly are you having trouble with? Are you having trouble with the chart showing correct data?
In report's you have 3 tables, and one of them not related to others.

Look for Link Master fields and Link child fields. cod not present in chart row source.
ano is numeric, code is text. Modified both to ano, gave some result so check and draw the required output.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

João serras-pereiraAuthor Commented:
Thanks! But Something is deadly wrong.  It should should, one chart per "cod", on the x-axis, all years, from 2014 to 2027 and, on the Y axis the correspondent 3 "saldo...". What am I doing wrong?
João serras-pereiraAuthor Commented:
Please see below on my original database:

link fields

and its output....

João serras-pereiraAuthor Commented:
btw. I have checked the select for the chart and its output table and they look alright... Please see below:


select output
the issue is that, somehow, this table is not plotted but nothing is plotted...
João serras-pereiraAuthor Commented:
BTW 2:

I have pasted the select table (from the chart query) to excel and it plots perfect!
Sorry! Unable to comprehend the problem. The issue is hidden in so many details.

Tried to let the chart shows,

What interests us is the minimal objects that contribute to the understanding of the specific issue. This means uploading one report and relevant tables and queries with few representative test data.
João serras-pereiraAuthor Commented:
Ok! will do!!!! - just please wait to tomorrow...
João serras-pereiraAuthor Commented:
I am uploading a "light" database, striped out of all the queries, tables, forms and reports that are unrelated to the problem.

I also did cut off  almost all villages; just left a few ones that would help evidence my problems.

Now the problems:

1. Data is not plotted

On the report I need to print data for each village (field is "cod" which is the concatenation of the name and a code) and year.
Data Prints correctly and is on the query "Sustenta 05 Final"
at the end of each village (i.e. "cod") I need to plot, on a line chart, 3 values that are on the table and are printed; the columns containing the data are "SaldoInq", "SaldoCapitacao" and "SaldoEquilibrio".
In the end of the report, I need to print the grand total (i.e. the sum for all villages) and plot the same chart, but now, not filtered by cod, but containing all records.

The problem is that no matter how, the chart in the final of the report  plots as expected (sort of, but that problem nr 2) and ALL charts in the "COD" section of the report, are blank. It looks as if the data, when the filter is applied, on the source/link record property, "disappears; however, when I inspect the source table on the select statement of the query it looks perfect (!)

So here is the question: what am I wrongdoing?

2. Chart cannot be manipulated

I can put a second question to this one as it is not the same thing and just showed when I finally got the end of report section working:
no matter how I am not able to change the appearance of the chart itself. I wanted it to occupy the frame, but, when I double-click to edit, it zooms in as a big not editable object (!)

Thanks for any help
Thanks for this cut down version.
Check this, and try the following:
Remove all unnecessary fields from tables.
Change the field names to English.
João serras-pereiraAuthor Commented:
Hi -

Please find attached the new version. I have deleted  the fields not relevant to the report and changed, in the report the calculations by the direct source field on the query, as well as changed the relevant field names to English. The behavior is precisely the same... (sigh)

I am attaching the new version of the db
ano changed to year, but in report it was not changed, so the access asks for ano.

So let us ignore other table for now, and see Table1. We assume this table as the record source for the report. Fill the table with values needed to produce the chart. Modify if needed.
Try to manually sketch the required output with chart. Keep it simple to 10 records.
João serras-pereiraAuthor Commented:
(!) In my computer the report "[copy of sustenta....]" does not ask for [ano]... I have double checked it. I am using it because I wanted to have a version close to the original and make all the tests with you on a separate report. I am sorry to have forgotten to mention it to you. I kept the other fields because they are required for the report (the textual part).

anyway I will fill the [Table1] but that does not solve the problem. I do really need to combination of text and graphics...
João serras-pereiraAuthor Commented:
I sending The updated DB with the Tables 1 (Now a query) with the data. But hope is diminishing....
João serras-pereiraAuthor Commented:
Ah! What a surprise!!!!

It is almost working. Nevertheless, I have changed the source data link to "cod" and deleted the source data link of the chart in the end. now values are OK! What did you do???
João serras-pereiraAuthor Commented:
Thanks a lot for your help and I am closing the question and awarding the points.
No matter my best efforts, I could not replicate the solution in the database so in the end I can't do nothing with it. Somehow it works in the database you have provided, but refuses to work on mine. I even tried to setup a specific query only for the chart, equal to the "Table1Q" but it did not work. Always the same behavior (sigh)

So now I am looking for charting components; if you know of anyone that I may use/buy, please let me know. Right now I downloaded ChartFX (I used it in the past, but within Visual Studio) and Crystal Reports, to check out
kindest regards
I Graph Chart control in Access. It is confusing at first use, but then becomes a good tool for the job.

"I could not replicate the solution in the database"
Check this original file you uploaded. I added the cod field and formatted the chart.
right click - chart - edit
then right click - item like axis and format ...
If need more help, you may ask more questions.
João serras-pereiraAuthor Commented:
Thanks again and I see it is working. You know, I am a fan of ms/access and have been using the charts (mostly in reports) without any problem. I do not need charts with loads of bells and whistles so it is perfect. This is the first time I am experiencing a real problem.

The database is progressing and I had deleted the charts. So, I tried again. Generated the chart following all your advice and voilà...  back to square 1. The chart does not plot. I even tried to generate a chart without the "cod" filter and then put the filter.

I am uploading it just in case, but feel free to stop answering, or then we should arrange a means of awarding additional points...
kind regards


" back to square 1"

Not really, you are at the first steps to excellence, and bear in mind that confusion is a good sign of starting to understand.

The last database with new reports and chart, has the following.
The row source is a cross tab query, that may stop the graph from working as you envision.

The graph depends on a change of cod, which means it has to be placed in the cod footer, not in the detail section.
Once it is working, copy it and paste it in the report footer, remove the Link fields and it graphs the total value.

You may start a series of questions to perform many tasks including graphing data. I am sure, following this path,  you will be answering and helping others in the near future. This does not rule out that you are already excelling in other areas and other people are looking for your assistance.

Good luck!
João serras-pereiraAuthor Commented:
Thanks again!
The graph was already in the COD footer section... and you are right. It was a crosstab query that I changed to a standard query (the crosstab was automatically set by the chart control when I dragged the series to the "data" box in the wizard.
The only issue is really that, when I put the COD filter, the chart does not show (sigh).

So thanks anyway!!!!

While trying to summarize the graph chart issue, I noticed that  I missed an important issue for the total graph chart.

Check Report1 based on Table1Q

The graph chart raw source per cod is:
SELECT [Year],[Total1],[Total2],[Total3] FROM [Table1Q];

For the total:
SELECT Table1Q.[Year], Sum(Table1Q.[Total1]) AS SumOfTotal1, Sum(Table1Q.[Total2]) AS SumOfTotal2, Sum(Table1Q.[Total3]) AS SumOfTotal3 FROM Table1Q GROUP BY Table1Q.[Year];
João serras-pereiraAuthor Commented:
Thanks!!! Using that the total now totals as expected...

BTW just fyi - I sorted out the issue in a real quick manner using Crystal Reports (downloaded a trial version).  Still, my personal preference goes to Microsoft Chart (more options and, of course, really integrated). Crystal Reports is a bit cumbersome, requires me to convert the database to an old format and loses synch with the database; but at least, the charts appear on the COD grouping section....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.