Counting column data

[Agartha]

I have a questionnaire table that has three columns, each column containing a textural answer to the column header question. Each answer is one of six textural choices that are specific to each column.

What I want to do is summarise the information so that I can read off how many of each answer there are e.g how many answered 'x' in quesiton 1, how many answered 'y' in quesion 2. If it helps, I don't require a summary that might detail how many answered ''x' to question 1 AND 'y' to questions 2. I just need column summaries.

Thanks

Agartha

 

[Brianwarnock]

Off the top of my head the only way I can think of is a query with 18 fields, 6 for each original column saying e.g.

hdr1valx:sum(iif(hdr1="x",1,0))

Brian

 

[bvan]

Sounds like a good use for a crosstab query. Questions on the x axis, Answers on the Y axis.

 

[Pat Hartman]

You need a normalized table to create a crosstab. If you have only three questions, you can create a normalized structure by using a union query. You can then use the union query to create the crosstabs. With a spreadsheet like structure, you have to jump through hoops to do any analysis.

Select "Q1" as Question, Q1Value, "Dummy" as ThirdField From YourTable
Union Select "Q2" as Question, Q2Value, "Dummy" as ThirdField From YourTable
Union Select "Q3" as Question, Q3Value, "Dummy" as ThirdField From YourTable;

Crosstab queries require three columns so I just made up the "ThirdField". That is the field you will tell the wizard you want to count.