?
Solved

Problem with charts in access reports

Posted on 2006-04-19
11
Medium Priority
?
267 Views
Last Modified: 2013-11-28
Hi
I've got a fairly simple database that I'm having to build for work (my access ability can also be described as simple - I'm the IT do-it-all for my Charity so please use language a kid can understand). I've got tables that link people with a questionnaire (survey) that they fill out every year and the results. There's a one to many relationship defined between the personId in Table Person and the personID in table results - so one person is linked to every questionnaire they completed and the results of each section.

From this I can build a report that lists each person, the questionnaire date and the average score for each section of the questionnaire. There are 8 sections.

So the report looks like this:

Annie Person
                                                                  Sec1       Sec2         Sec3      Sec4     Sec5      Sec6     Sec7     Sec8
                   Questionnaire Date: 26/5/05          7           8              5            6          7            5          6          5
                   Questionnaire Date: 26/5/05          5           6              6            4          5            7          8          5

Next Person
                                                                   Sec1       Sec2         Sec3      Sec4     Sec5      Sec6     Sec7     Sec8
                   Questionnaire Date: 26/5/05          7           8              5            6          7            5          6          5
                   Questionnaire Date: 26/5/05          5           6              6            4          5            7          8          5

Ok - the problem I have is that I need to get a chart into this report that will plot the results against an 8 axis radar chart (looks like a spiders web) - with the series based on each year. This chart has to be per person so one chart per person that will update itself based on the data that's added every year. I am getting nowhere with the evil chart wizard and suspect I'm way off track. Can anyone help me do this? I would be SO grateful,
Thanks
Deb



By the way I'm using Access 2003.


                   
0
Comment
Question by:Debsyl99
  • 5
  • 4
  • 2
11 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16495020
This link is a good place for you to start.  Note that it says "in order to use the chart wizard, you must have ms graph (vs ms chart).  You want a global chart and should follow applicable steps for a global chart.

      http://office.microsoft.com/en-ca/assistance/HP051888541033.aspx
 
0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16498556
Hi
That helps to a degree but the main problem's with the way I've got the tables structured. I've got msgraph and that works ok. Until I get the table structures and relationships right this isn't going to work anyway. Sorry.
Thanks
Deb


0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16498767
This should help get you started with your database restructuring:

This link contains a step by step tutorial for building a simple survey db using a survey on ms access to illustrate
                           http://www.functionx.com/access/applications/survey.htm
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 20

Author Comment

by:Debsyl99
ID: 16498851
Thanks - I'll have a look at that now :)
0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16499214
I wish mine was that simple - it isn't :(

With the charts I need to get the data out as follows:

Questionnaire Date (series)
Sections (there are 8 altogether) - axis of radar chart
Results - this is an average of the all the question results for each section.

If I could creat a query or something that I can use to pull this data into the chart wizard I might be on my way. However my table structure is a bit on the complex side for my access ability

http://www.ghost-watch.co.uk/database.html (not a plug for my site by the way - this one's not up or going anywhere)

I've posted a jpeg of the database relationships at the link above which might show better what I'm on about. The table "results" was linked in as well - was just getting a bit unwieldy for me!

Any idea as to which direction I should be going with this (please don't say towards the bin - if I could I would)

Any more info - please ask and thanks again

Deb :)

0
 
LVL 58

Expert Comment

by:harfang
ID: 16500359
Hello,

The chart wizard is one of the most difficult wizards to use... It can be done, if you start with a query that already links all your tables. However, the best is to fine-tune it by hand. Create any chart, and then redo everything "by hand".

The basic data sheet you need is this:

(For a given person, e.g. Annie Person)
Year     Sec1     Sec2     Sec3     Sec4     Sec5     Sec6     Sec7     Sec8
2005      4.5       5.2       5.3       6.7       3.5       5.6       4.1       8.9
2006      3.9       5.9       6.9       8.0       4.5       4.2       3.1       7.3

As you have created the report in the question, I'm sure you can manage that.
* Row header is: Year(QuestionaireDate)
* Col header is: 'Sec' & SectionNo
* Value is: Avg(Result)
* In the query properties, set Column Headings: Sec1;Sec2;Sec3;Sec4;Sec5;Sec6;Sec7;Sec8

Now, this is the query that you will use for the chart. Paste the sample above in the chart's datasheet, so that you can format your colors, sizes etc.

Then set these LinkMasterFields and LinkChildFields: ServiceUserID

Normally, that would be it. The chart would be on the header or footer for a person (so ServiceUserID would be available for the link), and the chart would be automatically requeried and redrawn each time.

Does that help?
(°v°)
0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16500849
Hi - Thanks for your reply Harfang and I'll do that as soon as I'm at the stage that you think I'm already at - the problem I have is this:

My original attempt to produce this:

Year     Sec1     Sec2     Sec3     Sec4     Sec5     Sec6     Sec7     Sec8
2005      4.5       5.2       5.3       6.7       3.5       5.6       4.1       8.9
2006      3.9       5.9       6.9       8.0       4.5       4.2       3.1       7.3

Came from an attempt at creating a query that averaged out the results for each of between 6 and 8 answers in each of 8 sections. However I had all these stored in one table at the time. So I tried to reduce duplicated data in the tables and ended up with what you can see in the link above.  So - I need to get this sorted in the right order.

I have stacks of points and I'm not afraid to use them as my neck is on the line with this.  At the moment I need to make sure my tables are structured right.  What I don't want to do is get too much help on too many diverse things in one question. What I might do if you guys think you can help and if it's not an EE contravention is post all the stuff I need to do on the weblink so you understand the idea behind it - then get it sorted in bite sized 500pt question chunks at the points I'm struggling at. Basically this database was due last year and for one reason or another we've been let down by developers we've tried to engage with it. I need to get something up that does the job at least to a limited extent within the next few weeks as unfortunately I'm responsible for all matters IT and our latest developer hasn't yet got back to me.

Alternatively I don't suppose you know any good honest reliable uk based access developers that want a bit of work do you? If it's any incentive at all we're a charity (non-profit) that provide services for some very vulnerable sections of society but we do have some money for this.

I'll post again in a bit once you let me know your views

Thanks again
Deb :)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16502695
Hello,

Based on the data structure, it would seem that linking the tables [Questionnaire...?], SectionNo, and QuestionNo would provide the fields needed for the query, i.e. ServiceUserID (for the filtering of the current person), QuestionnaireDate, SectionNo, and Result.

Can't you get the cross-tab to work? Or do you have another problem at this point?

PS: Let me show you a "normalized" data structure for a questionnaire, back in a while.

Cheers!
(°v°)
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 16503060
Here you go: http://www.harfang.ch/expert/Q_21819585.zip
You will notice that I used multiple key fields. For very large sets of data, it might be better to recreate a new autonumber key for each table. In this case, the multiple keys make the data structure much more understandable.

There is a sample report with a radar chart, of course.

Good luck!
(°v°)
0
 
LVL 20

Author Comment

by:Debsyl99
ID: 16503266
Wow - you are a total star! See you soon no doubt and thank you so very much - you've more than earned the points.
Thanks again and best wishes
Deb
0
 
LVL 58

Expert Comment

by:harfang
ID: 16503713
Glad to help! Good luck with your project!
(°v°)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

850 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