Totals and percentages in same query 1

[czb]

I am unfamiliar with Access and need to do some simple analysis of responses to a questionnaire. I have a table which includes a column containing responses with a value between 0 and 5 where 0 is actually a null response.

What is the form of query which allows me to show the number of responses for each value between 1 and 5 and also the percentage of the total valid (excluding the 0 valued null) responses for each value?

Thanks and regards

czb

 

[cmmrfrds]

If you make the 0 a null it won't get included in the calculation. I am not exactly sure what you want to show but you can use this as a model.

select response, avg(iif(response=0,response,null)) as avg1,
count(response) as cnt from table
group by response

 

[czb]

Thanks to cmmrfrds for prompt response but I obviously didn't explain properly what I am trying to do so here goes again:

I have a query:

SELECT locations1.[149gen info] AS rating,count( [locations1]![149gen info]) as count
FROM locations1
GROUP BY locations1.[149gen info]
HAVING (((locations1.[149gen info])between 1 and 5));

which returns two columns as below:

rating count
1 34
2 9
3 9
4 13
5 42

What I want to do is generate a third column which for each rating returns a figure which is the count for each rating as a percentage of the total counts.

I'm sure this is easy but I can't figure it out.

Help!

Thanks and regards

czb

 

[raskew]

Surely you have more than question in your questionnaire, so it would seem that question number should also be a factor.

Wish I were more proficient with sub-queries because I know it can be done with just one query. Lacking that expertise, here's how to do it with two queries.

The table (tblMyCount):

[table]
question	rating	count
1	1	34
1	2	9
1	3	9
1	4	13
1	5	42
2	1	10
2	2	17
2	3	19
[/table]

Query139:

SELECT x.question, Sum(x.count) AS SumOfcount
FROM tblMyCount AS x
GROUP BY x.question;

Query138:

SELECT tblMyCount.question, tblMyCount.rating, tblMyCount.count, [count]/[sumofcount] AS perc
FROM tblMyCount INNER JOIN Query139 ON tblMyCount.question = Query139.question
GROUP BY tblMyCount.question, tblMyCount.rating, tblMyCount.count, [count]/[sumofcount];

With Query138 in design mode, right-click on the Perc calculated field, select Format and enter Percent. Run the query and, with luck, you should end up with this:

[table]
question	rating	count	perc
1	1	34	31.78%
1	2	9	8.41%
1	3	9	8.41%
1	4	13	12.15%
1	5	42	39.25%
2	1	10	21.74%
2	2	17	36.96%
2	3	19	41.30%
[/table]

Please excuse the formatting, but you'll hopefully get the drift.

Best wishes,

Bob

 

[cmmrfrds]

SELECT A.response, count(A.response) as cnt,
(Select count(*)
FROM Testtable3 as B
where B.response > 0) as tot,
cnt/tot as perc
From testtable3 as A
where A.response > 0
GROUP BY A.response;