Calculating % in a query

[postmanphat]

Hi,

I have a very basic table/form that collects evaluations - I want to be able to analyse some basic data

I have a lookup field that lists 5 values: Excellent; Good; Ok; Poor; Very Poor.

So lets say my table (tblQ) looks like this:

AutoID     Response
-------------------------------
1          Excellent
2          Good 
3          OK 
4          OK

I've got a v. basic query that will count how many of each responses there are (SELECT response, Count(response) FROM tblQ GROUP BY response) which gives me a nice little breakdown like so:

Response   Count
-------------------
Excellent  1
Good       1
OK         2

What I want is an extra column in my query that gives me the % of total responses for that response, i.e.:

Response   Count   Percentage
---------------------------
Excellent  1       25
Good       1       25 
OK         2       50

But I can't work it out!

Hope thats clear.

Many thanks in advance

Dave

 

[postmanphat]

Typical!

2 minutes after I post this I stumble upon the answer!!!

Thanks anyway - I hope nobody has wasted their time!

Dave

 

[PHV]

For anyone who search the answer:
SELECT response, Count(*) AS [Count], 100*Count(*)/(SELECT Count(*) FROM tblQ) AS [Percentage]
FROM tblQ
GROUP BY response

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886