graph creating problem

  • Thread starter Thread starter Alex
  • Start date Start date
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
 
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...
 
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...
 
Back
Top