Converting to a Percent

[Guest]

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

 

[Steve Schapel]

Angeline,

I don't understand. Calculate what as a percentage of what? Maybe an
example would help.

 

[Guest]

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.

 

[Guest]

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

 

[Steve Schapel]

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")

 

[Guest]

Thanks Steve,
Yes I see that logic - great idea, thank you very much!

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")

--
Steve Schapel, Microsoft Access MVP

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.