Question about percents and grouping

[CriticalJ]

Suppose that I have a table that holds survey results. The survey has
10 questions, each with 3 results (say A, B, C). The survey is given
out at different locations. I need to build a report that gives the
total response for each question by site along with the percent.
Something like the following:

site 01
q1
A: 5 %50
B: 3 %30
C: 2 %20
q2
A: 6 %60
B: 1 %10
C: 3 %30
Etc.

Is there an easy way to do this?

My current method is kind of time consuming and klunky. I have built
a query for each question that gives me the response count by
location. I then union all of the individual queries into a big query
and calculate the percents on the report. It would be nice to find
something a little slicker.

 

[tina]

have you tried using a single Totals query, grouping on the site and result
fields, in that order, and counting another field?

hth

 

[CriticalJ]

No, I have not. Let me look into that.

Thanks.

J

 

[tina]

you're welcome

 

[John Spencer]

It kind of depends on the structure of your tables. If you have a table
with fields like Q1, Q2, Q3, ..., Q10 then the problem is very different
then if you have a structure like

TableResponses
SiteID
QuestionNum
Response

The above Table structure is fairly simple to get the results for

SELECT SiteID
, QuestionNum
, Response
, Count(Response) as ResponseCount
FROM TableResponses
GROUP BY SiteID, QuestionNum, Response

And if you need to get percentages you could do that with a subquery
embedded in the from clause.

SELECT TableResponses.SiteID
, TableResponses.QuestionNum
, Response
, Count(Response) as ResponseCount
, Count(Response) / RTotal as PercentScore
FROM TableResponses INNER JOIN
(SELECT SiteID, QuestionNum, Count(Response) as TotalResponses
FROM TableResponses
GROUP BY SiteID, QuestionNum) as RTotal
ON TableResponses.SiteID = RTotal.SiteID
AND TableResponses.QuestionNum = RTotal.QuestionNum
GROUP BY TableResponses.SiteID, TableResponses.QuestionNum,
TableResponses.Response

So first what is your table structure? If it is like the the table
described above, we can lead you through building a query. If it is
more like the first description, we can use a union query to normalize
the data and then use the union query to build the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[CriticalJ]

I misunderstood the first suggestion. I was already using a totals
query. The problem lies with the second suggestion. The table
structure is as follows:

id
site
q1
q2
....
q10

When you group by site and one question, it works fine. As you add
more questions, the group by functionality start expanding the
selection. The solution I was hinting at above is working.

Basically I have been converting the existing table structure to the
following structure (like the second post)

site
question
response
responsecount

using a series of unions.

The report is working fine. It is just tedious and I thought there
might be an elegant solution that I was missing.

Thanks again.

 

[John Spencer]

So you could use a union query like
SELECT Site, "q1" as QuestionNum, q1 as Response
FROM YourTable
UNION ALL
SELECT Site, "q2" as QuestionNum, q2 as Response
FROM YourTable
UNION ALL
....
SELECT Site, "q10" as QuestionNum, q10 as Response
FROM YourTable

With that query saved, you would have the basis for the query I proposed.

(Replace TableResponses with the name of the saved query)

SELECT TableResponses.SiteID
, TableResponses.QuestionNum
, Response
, Count(Response) as ResponseCount
, Count(Response) / RTotal as PercentScore
FROM TableResponses INNER JOIN
(SELECT SiteID, QuestionNum, Count(Response) as TotalResponses
FROM TableResponses
GROUP BY SiteID, QuestionNum) as RTotal
ON TableResponses.SiteID = RTotal.SiteID
AND TableResponses.QuestionNum = RTotal.QuestionNum
GROUP BY TableResponses.SiteID
, TableResponses.QuestionNum
, TableResponses.Response

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[CriticalJ]

SELECT Site, "q1" as QuestionNum, q1 as Response
FROM YourTable
UNION ALL
SELECT Site, "q2" as QuestionNum, q2 as Response
FROM YourTable
UNION ALL
....
SELECT Site, "q10" as QuestionNum, q10 as Response
FROM YourTable

Right. That is what I am doing and it works fine. Just looking for a
more elegant solution. I suspect I'll just stick with this method.
Modifications shouldn't be too difficult to maintain.

Thanks for your help.

 

[John Spencer]

I don't think there is a more elegant solution, unless you consider
redesigning your table structure a more elegant solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[CriticalJ]

"I don't think there is a more elegant solution, unless you consider
redesigning your table structure a more elegant solution. "

Right. Alas, as I am sure you all understand, some decisions are
kind of out my hands. The people who want this data are used to data
in an "excel" format. I'll just use the union approach.

Thanks again.