If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
graph creating problem
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","ItemAli as=[txtItemAlias] and Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItem]")*2+DCount("AnswerAlias","qrySurvey","ItemAli as=[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(Answer Alias)=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 |
#2
|
|||
|
|||
graph creating problem
There are pie charts in one of the reports in the At Your Survey demo
application found at http://www.rogersaccesslibrary.com/f...9f3 5afed7c97. -- Duane Hookom Microsoft Access MVP "Alex" wrote: 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","ItemAli as=[txtItemAlias] and Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItem]")*2+DCount("AnswerAlias","qrySurvey","ItemAli as=[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(Answer Alias)=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 |
#3
|
|||
|
|||
graph creating problem
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" wrote: There are pie charts in one of the reports in the At Your Survey demo application found at http://www.rogersaccesslibrary.com/f...9f3 5afed7c97. -- Duane Hookom Microsoft Access MVP "Alex" wrote: 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","ItemAli as=[txtItemAlias] and Cint(AnswerAlias)=2 and SurvGroup=[txtSurvGroupAll] AND Category=[txtCategoryItem]")*2+DCount("AnswerAlias","qrySurvey","ItemAli as=[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(Answer Alias)=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 |
Thread Tools | |
Display Modes | |
|
|