Need help writing a query 2

[njb]

I could use suggestions on how to write this query. A bit of background first. I work for a small rural county government and they are surveying residents about their satisfaction with their trash company. Once they fill out the survey, it needs to pop up with the overall survery results for that company. (35% agree, 30% agree strongly, etc for each question). I don't expect they will get a huge response, probably a couple of hundred, so I don't think it will take long for a query to tabulate the statistics.

The record (tblTrashPollResponses) looks like this:

Key (autonumber)
Company
Name
Address
Company
Q2 (this contains the results of Question #2. The value 0 = No response, 1 = Agrees Strongly, 2 = Agrees Somewhat, 3 = Disagree Somewhat, 4 = Disagree Strongly, 5 = Unknown)
Q3 (questions 3 - 8 are similiar to question 2)
Q4
Q5
Q6
Q7
Q8

I have the online form written and the database is being updated correctly. I had to assign a number value to the responses for each question because I used radio buttons for the responses.

What I need is a query that will return the following. I don't care if it is one big record or if I need to read a recordset.

For the requested company:
"Question 2", the total number of Agree Strongly responses and what % that represents, the total number of Agree Somewhat responses and what % that represents, etc.

I've been trying to write this in Access and I just can't figure it out. I presume I will need to use a crosstab of some sort. Can anybody help me? Please???

Thanks in advance
Norma

 

[r937]

select sum(iif(q2=1,1,0)) as agrees_strongly
     , sum(iif(q2=1,1,0)) * 100.0
         / count(*) as agrees_strongly_pct
     , sum(iif(q2=1,2,0)) as agrees_somewhat
     , sum(iif(q2=1,2,0)) * 100.0
         / count(*) as agrees_somewhat_pct
  from tblTrashPollResponses
 where Company = 'foo'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[njb]

You are AWESOME!! I would never have come up with this solution. Thank-you so much. You've saved me a lot of time. Now I can finally move forward.

Hope you have a great day. No make that year!

 

[r937]

aaargh, i got it wrong, sorry

select sum(iif(q2=1,1,0)) as agrees_strongly
     , sum(iif(q2=1,1,0)) * 100.0
         / count(*) as agrees_strongly_pct
     , sum(iif(q2=[COLOR=red]2[/color],1,0)) as agrees_somewhat
     , sum(iif(q2=[COLOR=red]2[/color],1,0)) * 100.0
         / count(*) as agrees_somewhat_pct
  from tblTrashPollResponses
 where Company = 'foo'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[njb]

Yes, I discovered that but once I dissected your solution, I was able to fix it. (I was getting all zeroes) Tommorrow I will write the asp page and then the user can approve it. You did a great job of pointing me in the right direction and have been a big help. It's a good ending to the day. Thanks again!

 

[lespaul]

then give a star!!

Leslie

 

[njb]

I couldn't figure out how to do the star! Got it now. )