Grouping COUNT() help

highbulp

Registered User.
Local time
Today, 14:30
Joined
Oct 10, 2006
Messages
15
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:

Code:
---------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.
 
If you don't want to develop 20 of everything, you're going to need to normalize the tables. The answer table will be one row per person per question.

Sam q1 a
Sam q2 b
Sam q3 a
..
Pat q1 c
Pat q2 a
Pat q3 a
...
etc.

This structure will allow you to use a single query to evaluate the responses. You can use a crosstab query to create a display similar to what you show as your input.
 
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.
 
Rather than struggle with alternate methods to avoid properly structuring your data, it will be far easier in the long run to just fix the data structure. There are several suggested structures here for questionaire and survey databases that should give you some ideas.

One possible crutch is to use a union query. This may solve your problem as long as you don't have too many questions.

Select KeyFlds, "Q1" As Question, Q1 As Answer From YourTable
Union Select KeyFlds, "Q2" As Question, Q2 As Answer From YourTable
Union Select KeyFlds, "Q3" As Question, Q3 As Answer From YourTable
Union Select KeyFlds, "Q4" As Question, Q4 As Answer From YourTable
...
 
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")?
 
DCount() counts the rows in the domain. You can use a query or table to specify the domain and you can use criteria to limit it. There is no way to reference the report's RecordSource as the domain but you can reference the query you use as the RecordSource or the table if you don't use a query.

Using domain functions in queries or reports is not recommended if you have a lot of data. Remember, each domain function runs its own query. So, if you have 1000 rows in your recordset, you will be running 1000 queries within the scope of the original query.

So as I said, changing the structure is not feasible.
You can't change the import but you can change the structure in the Access database. You can do it EVERY time you need to run a query by using the union method from my last post or you can use the union method to make a normalized table and then use that table for all your queries.
 
Well I've managed to get things working how I wanted. Thanks for all your help.
 

Users who are viewing this thread

Back
Top Bottom