Grouping COUNT() help

[highbulp]

I have a set of evaluation response data. There are a number of question (like 20), and each respondent answered the question on a scale of 1 to 5. Thus I have a table Responses where each column is a question and each row is a Respondent's answers. Each cell has a value between 1 and 5 (inclusive), or is possibly NULL.

What I want is to be able to list out the number of people who responded with a certain evaluation for each question. So it would list the number of people who gave a 1, a 2, a 3, and so on for each question. So I'd get something like:

---------Q1  Q2  Q3  Q4  Q5 ...
   -1-    x   x   x   x   x ...
   -2-    x   x   x   x   x ...
   -3-    x   x   x   x   x ...
   -4-    x   x   x   x   x ... 
   -5-    x   x   x   x   x ...

Ideally I'd like the questions to be on the rows and the numbers as columns, but I can just do the transposition when I make this as a report.

Any ideas of a SQL Query that will get me such a table? I'd really like to avoid VBA if possible (I'm writing this database to be used/maintained by a non-programmer), and I'd like to not have to develop like 20 different subreports in order to print out this information.

Thanks.

 

[highbulp]

In other words, I need to have which question as an element of data in the table, rather than just implied through the table's structure? So I have to be able to check if that "5" applies to Q1 rather than checking that the "5" is in Column Q1. Is that right?

It is also not feasible to change how my data table is layed out. Is it possible to use an intermediate query to create another table or view that is layed out as you suggest? I have a feeling it's probably not.

I think what I want to do is fundamentally opposed to the structure of SQL. I want to fetch entries IN a datatable rather than fetching the rows OF a datatable. I guess I'm just too used to Excel or something. I think I'll probably look at a different approach, namely creating a number of small Queries or Functions inside a Report.

Thanks anyway.

 

[highbulp]

Unfortunately, we already have a survey import system set-up, and I don't think I can change that. The output it gives is a .csv of the specified format. I can import that into Access no problem, but now I'm having trouble accessing the data in the way that I want. So as I said, changing the structure is not feasible.

Can you answer another question? Is there a way to reference the table that is the source for a Report from inside a DCount() call? So have the call look like DCount("[myfield]","[ReportSource]","condition")?

 

[highbulp]

Well I've managed to get things working how I wanted. Thanks for all your help.