group by and count

[Nydia]

I created a database for a survey. I created 4 tables
1. tblsurveysays who- this table has the department name,
employee id, union code (there are only 3 union codes,
m=management, u=union, a=administration) and what year
this survey is answered.

table 2 is questions. this table has employee id (which is
a 1 to many relationship with table 1 employee id),
question #, question answer and question comments. the
question answer is a combo box that lets you pick from an
answer in table 4.

table 3 just has question# and question
table 4 is answer choice (agree, disagree, strongly
disagree and strongly agree)

what i am trying to do is run a query that will show the
question and how many union people answered it with
agree/disagree/etc.

example
question1 question2 question3
agree 5 management 3management 4management
1 union 3union 1union

disagree same as above

 

[Michel Walsh]

Hi,

How Table4 is linked to Table2? there share a field in common? Anyhow,
since you didn't supplied them, I would assume table4 fields are:

WhoID, QuestionNumber, Answer ' Fields
1 1 1
1 2 1
1 3 3
....
4 1 4
.... ' data

which is read as WhoId=1 answered 1 to question 1, also 1 to question 2,
and 3 to question 3 ... whoID=4 answered 4 to question 1, etc.


Make a query like:

SELECT Table4.*, table1.unionCode
FROM table4 INNER JOIN table1 ON table1.WhoID=table4.WhoID


Save it, for illustration, say under the name Qin. That query supply the
UnionCode number in addition to the questions-answers. Note that UNION is a
reserved word, in SQL, so, if you field is called UNION, you will have to
use [Union], ie, to include it within square brackets... to differentiate it
from the keyword.

Next, start the query crosstab wizard. You would group on UnionCode AND on
Answer ( the two fields together defined the groups), you would pivot on
QuestionNumber, and you will aggregate with a COUNT on WhoID.


Hoping it may help,
Vanderghast, Access MVP