 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005Converting to a Percent
Hi, I am fumbling around building a database without actually knowing SQL but using the tools in ACCESS. I have written a query which counts the number of values in a field. I would like it to calculate it as a percentage but not quite sure how to do this. Remember I do not know SQL so if there is a way I can do it through the 'build' option within query. I remembered that I did do this upon creating a database a few years back. I had to tell it to divide the value of one field with the sum of all fields and then change the format to 'percent'. But when I try to do this I get the message that the <field name> is not part of the aggregate function. Hope someone can help Thankyou Angeline Angeline, I don't understand. Calculate what as a percentage of what? Maybe an example would help.  SignatureSteve Schapel, Microsoft Access MVP > Hi, > I am fumbling around building a database without actually knowing SQL but [quoted text clipped - 13 lines] > Thankyou > Angeline Steve, Apologies. I shall explain. I have a table of data which are survey results. I am trying to write a query which will show me the percentage of people that answered "yes", "no" and "neutral" to a particular question. I can write a query which will 'count' the number of 'yes' 'no' and 'neutral' responses, but I would really like it to tell me the percentage instead. ACCESS needs to divide the number of "yes" responses by the total number of responses in order to get the percentage. I think that maybe I need to write a query off a query but each time I attempt this I seem to be getting syntax errors. Does this help? Thanks so much for your help. > Angeline, > [quoted text clipped - 18 lines] > > Thankyou > > Angeline Angeline, A couple of possibilities... Make a query that simply gives you a total count of survey responses. Then add this query to your existing query where you do the count for each category, which will allow a percentage column in the query something like this... Percentage: Count([Response])/[CountOfResponses] Use a domain aggregate function, for example... Percentage: Count([Response])/DCount("*","YourTableName") SignatureSteve Schapel, Microsoft Access MVP > Steve, > Apologies. I shall explain. [quoted text clipped - 9 lines] > Does this help? > Thanks so much for your help. Thanks Steve, Yes I see that logic - great idea, thank you very much! Angeline > Angeline, > [quoted text clipped - 22 lines] > > Does this help? > > Thanks so much for your help. Steve, Apologies I will explain. I have a table which is actually the results of a survey. I have written a query which counts the number of "yes", "no" and "neutral" responses to a particular survey question. I would really like to know how to write the query so that rather than give me the number of responses, it gives me the percentage. Therefore ACCESS would need to divide, the number of "yes" responses by the total number of responses and likewise for "no" and "neutral". How do I get ACCESS to do this, do I need to write a query on a query ? Thanks for your help! Angeline > Angeline, > [quoted text clipped - 18 lines] > > Thankyou > > Angeline |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.