Problem with charts in access reports

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.


                   
LVL 20
Debsyl99Asked:
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.

puppydogbuddyCommented:
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
Debsyl99Author Commented:
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
puppydogbuddyCommented:
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
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.

Debsyl99Author Commented:
Thanks - I'll have a look at that now :)
0
Debsyl99Author Commented:
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
harfangCommented:
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
Debsyl99Author Commented:
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
harfangCommented:
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
harfangCommented:
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

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
Debsyl99Author Commented:
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
harfangCommented:
Glad to help! Good luck with your project!
(°v°)
0
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.

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.