Totals in Report

[Zippyfrog]

I am working on a database that collects results from a survey. The survey is generated into an Excel spreadsheet and I imported the excel spreadsheet to a table.

The purpose of this survey is for students to rate teachers on a number of questions on a scale from 1-5, with 1 being poor and 5 being excellent. What I need to be able to do is for each teacher, create a report that for each question, calculates the number of students that answered 1,2,3,4 or 5 for each question. I am runnning into trouble in doing this. I wanted to set up a total query, but I can't get the count function to work, and I think it has to do with my setup. I know how to setup the report once I have the query working like I want, but I am having trouble with the query counting per question per teacher per period.

Here is what I want my report to look like, lined up better of course! I attached I copy of my database so you can see my setup (it is a separate page of the report for each teacher each period):

                         Excellent      Very Good     Average    Below Avg.   Poor
Question#1               4                 5               2            1             4
Question#2               2                 3               1            5             2
Question#3               3                 2               3            2           2

 

[Banana]

I think what you want is a crosstab query. In database window, click on "New" query which you can choose Crosstab Query Wizard to help you with that.

Just make sure you have two things: the list of questions and list of responses, put them together in a simple query to be used for crosstab query wizard. It'll then return just as you wanted above.

 

[Zippyfrog]

Thanks once again for your help! The only issue is that I can only choose 3 fields, but ultimately I would need 5 - one for each level (excellent, above avg, avg, below avg., poor.) How would I go about working with that?

 

[Banana]

Finish the crosstab. Then go back and edit it. You then can add more fields; follow the other existing fields' lead. I only recommended wizard just to help get the basic crosstab query set up so you can see how it works from the design view.

 

[Zippyfrog]

Thanks. I am slowly coming along. How do I go about selecting the column headings when the column headings are the results in the field, and not the field itself? Each of the questions should be a row, and the values should be the number of times an answer was chosen for a teacher on a given survey question.

 

[Banana]

When I gave out the response earlier, it belatedly occured to me that I should clarify- you want your column heading and row heading to be the list itself which will then spread out.

So in design grid, you only need:

Questions
Row Heading

Scores
Column Heading.

This will then transform into this:

      1     2    3    4    5
Question1
Question2
Question3

HTH.

 

[Zippyfrog]

Can this be done with a single, flat file, non-relational table? I think that is where I am having my trouble understanding how to set this up. The table is imported from Excel, so it doesn't have any relations built into it.

 

[Banana]

In that case, you need to use VBA to describe how to import data from Excel. For example, you'd tell VBA to go through the Question column downward and add Questions to a new Question table, then have VBA go through Score row rightward to add to a new Score table.

The thing is I don't know how to do that in Excel; maybe if you search in Excel forum, you can find how to do just that.