Solved

ms/access charts

Posted on 2014-12-30
24
96 Views
Last Modified: 2015-01-04
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?
sireshDB-12.accdb
0
Comment
Question by:jirdeaid
  • 14
  • 9
24 Comments
 
LVL 84
ID: 40524673
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?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40524905
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.
sireshDB-12-2.mdb
0
 

Author Comment

by:jirdeaid
ID: 40525047
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?
0
 

Author Comment

by:jirdeaid
ID: 40525049
Please see below on my original database:

link fields

and its output....

output
0
 

Author Comment

by:jirdeaid
ID: 40525054
btw. I have checked the select for the chart and its output table and they look alright... Please see below:

select

select output
the issue is that, somehow, this table is not plotted but nothing is plotted...
0
 

Author Comment

by:jirdeaid
ID: 40525068
BTW 2:

I have pasted the select table (from the chart query) to excel and it plots perfect!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40525386
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.
sireshDB-12-03.mdb
0
 

Author Comment

by:jirdeaid
ID: 40525563
Ok! will do!!!! - just please wait to tomorrow...
0
 

Author Comment

by:jirdeaid
ID: 40526964
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
sireshDB-12-light.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40527040
Thanks for this cut down version.
Check this, and try the following:
Remove all unnecessary fields from tables.
Change the field names to English.
sireshDB-12-light-2.accdb
0
 

Author Comment

by:jirdeaid
ID: 40527334
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
sireshDB-12-light-3.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40527822
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.
sireshDB-12-light-4.accdb
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jirdeaid
ID: 40527979
(!) 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...
0
 

Author Comment

by:jirdeaid
ID: 40527985
I sending The updated DB with the Tables 1 (Now a query) with the data. But hope is diminishing....
sireshDB-12-light-5.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40528424
0
 

Author Comment

by:jirdeaid
ID: 40528464
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???
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 40528565
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!
sireshDB-12-light-7.accdb
0
 

Author Closing Comment

by:jirdeaid
ID: 40528945
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
joao
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40529168
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.
sireshDB-12-original.accdb
0
 

Author Comment

by:jirdeaid
ID: 40529429
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

joao
sireshDB-13.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40529617
@jirdeaid

" 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!
0
 

Author Comment

by:jirdeaid
ID: 40529933
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!!!!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40530492
Welcome!

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];
sireshDB-12-light-7-total.accdb
0
 

Author Comment

by:jirdeaid
ID: 40530658
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....
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now