graph creating problem

A

Alex

We have a survey where all questions rated as 1, 2, 3, 4, 5.

In this survey we want to calculate scores for questions as e.g. {(([number
of people] answered with rating 5) x 5+ (([number of people] answered with
rating 4) x 4)/[Number of all users taking a survey] )+ … }/[Number of all
users taking a survey], etc.

Then calculate scores for groups as we need to analyze it for different
groups and categories.

On a report I’m using groups such as Category as a high level group then
Survey Group then Questions group. I’m not using details area at all there.

So, the lowest group level on a report – ItemText (Questions) has a
calculation as :

=(DCount("AnswerAlias"," qrySurvey","ItemAlias=[txtItemAlias] and
Cint(AnswerAlias)=1 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*1+DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias]
and Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*2+DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias]
and Cint(AnswerAlias)=3 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*3+DCount("AnswerAlias"," qrySurvey
","ItemAlias=[txtItemAlias] and Cint(AnswerAlias)=4 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*4+DCount("AnswerAlias"," qrySurvey
","ItemAlias=[txtItemAlias] and Cint(AnswerAlias)=5 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*5)/DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias] and SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItem]")

Another (higher) group level calculation is:

=(DCount("AnswerAlias"," qrySurvey ","Cint(AnswerAlias)=1 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*1+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*2+DCount("AnswerAlias","qrySurvey","Cint(AnswerAlias)=3
and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*3+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=4 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*4+DCount("AnswerAlias","
qrySurvey","Cint(AnswerAlias)=5 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*5)/DCount("AnswerAlias","qrySurvey","SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItemAll]")

And the highest group Category has
=(DCount("AnswerAlias"," qrySurvey ","Cint(AnswerAlias)=1 and
Category=[txtCategoryAll]")*1+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=2 and
Category=[txtCategoryAll]")*2+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=3 and
Category=[txtCategoryAll]")*3+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=4 and
Category=[txtCategoryAll]")*4+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=5 and
Category=[txtCategoryAll]")*5)/DCount("AnswerAlias"," qrySurvey
","Category=[txtCategoryAll]")

qrySurvey is producing:
ItemAlias (Question#)
1
1
2
2
ItemText (Question Text):
vvvv
vvvv
…
…
AnswerAlias (Answer Score):
1
5
…
…
AnswerText:
Rrr
Bbb
…
Category:
Com
Empl
…
SurvGroup:
Lead
Manag
…

Everything is working perfectly on a report.

But, I’d need to create some graph as well that would show Category by
SurvGroup. E.g. Category = Com and SurvGroup = Lead and Manag with score % on
a Pie Chart.

Could anybody advised how I could do it?

Thanks
 
D

Duane Hookom

There are pie charts in one of the reports in the At Your Survey demo
application found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=1z5b6c5z9z1ezdb3357e9f35afed7c97.

--
Duane Hookom
Microsoft Access MVP


Alex said:
We have a survey where all questions rated as 1, 2, 3, 4, 5.

In this survey we want to calculate scores for questions as e.g. {(([number
of people] answered with rating 5) x 5+ (([number of people] answered with
rating 4) x 4)/[Number of all users taking a survey] )+ … }/[Number of all
users taking a survey], etc.

Then calculate scores for groups as we need to analyze it for different
groups and categories.

On a report I’m using groups such as Category as a high level group then
Survey Group then Questions group. I’m not using details area at all there.

So, the lowest group level on a report – ItemText (Questions) has a
calculation as :

=(DCount("AnswerAlias"," qrySurvey","ItemAlias=[txtItemAlias] and
Cint(AnswerAlias)=1 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*1+DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias]
and Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*2+DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias]
and Cint(AnswerAlias)=3 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*3+DCount("AnswerAlias"," qrySurvey
","ItemAlias=[txtItemAlias] and Cint(AnswerAlias)=4 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*4+DCount("AnswerAlias"," qrySurvey
","ItemAlias=[txtItemAlias] and Cint(AnswerAlias)=5 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*5)/DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias] and SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItem]")

Another (higher) group level calculation is:

=(DCount("AnswerAlias"," qrySurvey ","Cint(AnswerAlias)=1 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*1+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*2+DCount("AnswerAlias","qrySurvey","Cint(AnswerAlias)=3
and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*3+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=4 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*4+DCount("AnswerAlias","
qrySurvey","Cint(AnswerAlias)=5 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*5)/DCount("AnswerAlias","qrySurvey","SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItemAll]")

And the highest group Category has
=(DCount("AnswerAlias"," qrySurvey ","Cint(AnswerAlias)=1 and
Category=[txtCategoryAll]")*1+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=2 and
Category=[txtCategoryAll]")*2+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=3 and
Category=[txtCategoryAll]")*3+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=4 and
Category=[txtCategoryAll]")*4+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=5 and
Category=[txtCategoryAll]")*5)/DCount("AnswerAlias"," qrySurvey
","Category=[txtCategoryAll]")

qrySurvey is producing:
ItemAlias (Question#)
1
1
2
2
ItemText (Question Text):
vvvv
vvvv
…
…
AnswerAlias (Answer Score):
1
5
…
…
AnswerText:
Rrr
Bbb
…
Category:
Com
Empl
…
SurvGroup:
Lead
Manag
…

Everything is working perfectly on a report.

But, I’d need to create some graph as well that would show Category by
SurvGroup. E.g. Category = Com and SurvGroup = Lead and Manag with score % on
a Pie Chart.

Could anybody advised how I could do it?

Thanks
Click to expand...
 
A

Alex

Thanks Duane,

That link helped me to think how to resolve it. I've created a couple of
queries based on a main one and created my graphs.

Duane Hookom said:
There are pie charts in one of the reports in the At Your Survey demo
application found at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=1z5b6c5z9z1ezdb3357e9f35afed7c97.

--
Duane Hookom
Microsoft Access MVP


Alex said:
We have a survey where all questions rated as 1, 2, 3, 4, 5.

In this survey we want to calculate scores for questions as e.g. {(([number
of people] answered with rating 5) x 5+ (([number of people] answered with
rating 4) x 4)/[Number of all users taking a survey] )+ … }/[Number of all
users taking a survey], etc.

Then calculate scores for groups as we need to analyze it for different
groups and categories.

On a report I’m using groups such as Category as a high level group then
Survey Group then Questions group. I’m not using details area at all there.

So, the lowest group level on a report – ItemText (Questions) has a
calculation as :

=(DCount("AnswerAlias"," qrySurvey","ItemAlias=[txtItemAlias] and
Cint(AnswerAlias)=1 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*1+DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias]
and Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*2+DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias]
and Cint(AnswerAlias)=3 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*3+DCount("AnswerAlias"," qrySurvey
","ItemAlias=[txtItemAlias] and Cint(AnswerAlias)=4 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*4+DCount("AnswerAlias"," qrySurvey
","ItemAlias=[txtItemAlias] and Cint(AnswerAlias)=5 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItem]")*5)/DCount("AnswerAlias","qrySurvey","ItemAlias=[txtItemAlias] and SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItem]")

Another (higher) group level calculation is:

=(DCount("AnswerAlias"," qrySurvey ","Cint(AnswerAlias)=1 and
SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*1+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*2+DCount("AnswerAlias","qrySurvey","Cint(AnswerAlias)=3
and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*3+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=4 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*4+DCount("AnswerAlias","
qrySurvey","Cint(AnswerAlias)=5 and SurvGroup=[txtSurvGroupAll] AND
Category=[txtCategoryItemAll]")*5)/DCount("AnswerAlias","qrySurvey","SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItemAll]")

And the highest group Category has
=(DCount("AnswerAlias"," qrySurvey ","Cint(AnswerAlias)=1 and
Category=[txtCategoryAll]")*1+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=2 and
Category=[txtCategoryAll]")*2+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=3 and
Category=[txtCategoryAll]")*3+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=4 and
Category=[txtCategoryAll]")*4+DCount("AnswerAlias"," qrySurvey
","Cint(AnswerAlias)=5 and
Category=[txtCategoryAll]")*5)/DCount("AnswerAlias"," qrySurvey
","Category=[txtCategoryAll]")

qrySurvey is producing:
ItemAlias (Question#)
1
1
2
2
ItemText (Question Text):
vvvv
vvvv
…
…
AnswerAlias (Answer Score):
1
5
…
…
AnswerText:
Rrr
Bbb
…
Category:
Com
Empl
…
SurvGroup:
Lead
Manag
…

Everything is working perfectly on a report.

But, I’d need to create some graph as well that would show Category by
SurvGroup. E.g. Category = Com and SurvGroup = Lead and Manag with score % on
a Pie Chart.

Could anybody advised how I could do it?

Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Categories 1
Grouping not working right 2
Group in a horizontal line separated by commas 2
Creating a Bar Graph with a reference point 1
How to equally weight variables 2
Can't get soap client to connect in Excel 2010 64 bit 0
Modifying text boxes in a report? 1
I need ONLY subtotals copied to a second sheet 4

Top