Please help! Regarding design report of client feedback.

T

Terry

Hi,All
I am developing a client feedback program. Users can feedback clients
base on defined questions by the program, and fill in the answers into
program. The following table structure I designed is recording questions and
client's answers.

ClientID: number
question1: text(answer is: satisfy, so so, unsatisfy)
question2: text(answer is: yes or no)
....
question10: text(answer is: good, commonly, bad)

I designd a form base on above table, and feedback 65 clients by the form.
The next step, I must generate the feedback report. But how can I statistic
the data?
I try to statistic each question. The SQL query sentence is:
SELECT Count(ClientID) AS subtotal, question1
FROM tblFeedback
GROUP BY question1;

The result is:
subtotal question1
2 so so
62 satisfy
1 unsatisfy

But if I add qustion2 into above SQL query sentence:
SELECT Count(ClientID) AS subtotal, question1, question2
FROM tblFeedback
GROUP BY question1, question2;

The result is:
subtotal question1 question2
1 unsatisfy unsatisfy
2 unsatisfy satisfy
40 satisfy satisfy
5 satisfy so so
15 so so satisfy
2 so so so so

This result is NOT what I need! The result I expected is:
satisfy so so unsatisfy
question1 62 2 1
question2 62 1 3
question3 54 7 4
....
question10 57 7 1

Would you tell me how to do it and get above result? Thank you very much!!!
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to change the design of your table to this:

ClientID - a number
Question - a number: 1-n
Answer - Text

Then create a cross-tab (aka Pivot Table) query to get the count. E.g.:

TRANSFORM Count(*) As TheCount
SELECT Question, Count(*) AS subtotal
FROM tblFeedback
GROUP BY Question
PIVOT Answer

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQIgsoechKqOuFEgEQJJrgCg4aaHfEvq2E086NFvkFl3luu8quEAoINy
wHUFrrZnCg4ZYN9xrfzOH0cw
=CZBJ
-----END PGP SIGNATURE-----
 
T

Tom Wickerath

Hi Terry,

Your table design is not correct. You don't want a separate field for each question. You have a
many-to-many (M:N) relationship between clients and questions. A M:N relationship requires three
tables and is modeled by using two one-to-many (1:M) relationships, with the many side of each
relationship (ie. the "foreign keys") in a linking table. (Other names that I have seen for the
linking table include "Intersection" and "Join" tables).

tblClients
pkClientID <---Primary Key
ClientFirstName
ClientLastName
etc. (any other attributes about the client)

tblQuestions
pkQuestionID <---Primary Key
Question

tblAnswers (intersection table)
pkAnswerID <---Primary Key
fkClientID (foreign key)
fkQuestionID (foreign key)
Answer

After creating these three tables, populate tblClients with your client information and
tblQuestions with your questions. This is the minimum design, where one would type in the answer
(satisfy, so so, unsatisfy, yes, no, good, commonly, bad, etc.) into the answer field in
tblAnswers. You will likely want an expanded design that will limit the answers appropriately
for each question.

You can then use a crosstab query to produce the desired result:

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfy","so so","unsatisfy")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


You can also produce a total count of the answers for each question (since some clients may not
answer all questions):

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #],
Count(tblAnswers.Answer) AS [Total Responses]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfied","so so","unsatisfied")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


Tom
___________________________________


Hi,All
I am developing a client feedback program. Users can feedback clients
base on defined questions by the program, and fill in the answers into
program. The following table structure I designed is recording questions and
client's answers.

ClientID: number
question1: text(answer is: satisfy, so so, unsatisfy)
question2: text(answer is: yes or no)
....
question10: text(answer is: good, commonly, bad)

I designd a form base on above table, and feedback 65 clients by the form.
The next step, I must generate the feedback report. But how can I statistic
the data?
I try to statistic each question. The SQL query sentence is:
SELECT Count(ClientID) AS subtotal, question1
FROM tblFeedback
GROUP BY question1;

The result is:
subtotal question1
2 so so
62 satisfy
1 unsatisfy

But if I add qustion2 into above SQL query sentence:
SELECT Count(ClientID) AS subtotal, question1, question2
FROM tblFeedback
GROUP BY question1, question2;

The result is:
subtotal question1 question2
1 unsatisfy unsatisfy
2 unsatisfy satisfy
40 satisfy satisfy
5 satisfy so so
15 so so satisfy
2 so so so so

This result is NOT what I need! The result I expected is:
satisfy so so unsatisfy
question1 62 2 1
question2 62 1 3
question3 54 7 4
....
question10 57 7 1

Would you tell me how to do it and get above result? Thank you very much!!!
 
T

Tom Wickerath

PS.
If you'd like to know more about crosstab queries, check out this link:
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom
_______________________________________


Hi Terry,

Your table design is not correct. You don't want a separate field for each question. You have a
many-to-many (M:N) relationship between clients and questions. A M:N relationship requires three
tables and is modeled by using two one-to-many (1:M) relationships, with the many side of each
relationship (ie. the "foreign keys") in a linking table. (Other names that I have seen for the
linking table include "Intersection" and "Join" tables).

tblClients
pkClientID <---Primary Key
ClientFirstName
ClientLastName
etc. (any other attributes about the client)

tblQuestions
pkQuestionID <---Primary Key
Question

tblAnswers (intersection table)
pkAnswerID <---Primary Key
fkClientID (foreign key)
fkQuestionID (foreign key)
Answer

After creating these three tables, populate tblClients with your client information and
tblQuestions with your questions. This is the minimum design, where one would type in the answer
(satisfy, so so, unsatisfy, yes, no, good, commonly, bad, etc.) into the answer field in
tblAnswers. You will likely want an expanded design that will limit the answers appropriately
for each question.

You can then use a crosstab query to produce the desired result:

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfy","so so","unsatisfy")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


You can also produce a total count of the answers for each question (since some clients may not
answer all questions):

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #],
Count(tblAnswers.Answer) AS [Total Responses]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfied","so so","unsatisfied")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


Tom
___________________________________


Hi,All
I am developing a client feedback program. Users can feedback clients
base on defined questions by the program, and fill in the answers into
program. The following table structure I designed is recording questions and
client's answers.

ClientID: number
question1: text(answer is: satisfy, so so, unsatisfy)
question2: text(answer is: yes or no)
....
question10: text(answer is: good, commonly, bad)

I designd a form base on above table, and feedback 65 clients by the form.
The next step, I must generate the feedback report. But how can I statistic
the data?
I try to statistic each question. The SQL query sentence is:
SELECT Count(ClientID) AS subtotal, question1
FROM tblFeedback
GROUP BY question1;

The result is:
subtotal question1
2 so so
62 satisfy
1 unsatisfy

But if I add qustion2 into above SQL query sentence:
SELECT Count(ClientID) AS subtotal, question1, question2
FROM tblFeedback
GROUP BY question1, question2;

The result is:
subtotal question1 question2
1 unsatisfy unsatisfy
2 unsatisfy satisfy
40 satisfy satisfy
5 satisfy so so
15 so so satisfy
2 so so so so

This result is NOT what I need! The result I expected is:
satisfy so so unsatisfy
question1 62 2 1
question2 62 1 3
question3 54 7 4
....
question10 57 7 1

Would you tell me how to do it and get above result? Thank you very much!!!
 
T

Terry

Thank you very much!!!

Tom Wickerath said:
Hi Terry,

Your table design is not correct. You don't want a separate field for each question. You have a
many-to-many (M:N) relationship between clients and questions. A M:N relationship requires three
tables and is modeled by using two one-to-many (1:M) relationships, with the many side of each
relationship (ie. the "foreign keys") in a linking table. (Other names that I have seen for the
linking table include "Intersection" and "Join" tables).

tblClients
pkClientID <---Primary Key
ClientFirstName
ClientLastName
etc. (any other attributes about the client)

tblQuestions
pkQuestionID <---Primary Key
Question

tblAnswers (intersection table)
pkAnswerID <---Primary Key
fkClientID (foreign key)
fkQuestionID (foreign key)
Answer

After creating these three tables, populate tblClients with your client information and
tblQuestions with your questions. This is the minimum design, where one would type in the answer
(satisfy, so so, unsatisfy, yes, no, good, commonly, bad, etc.) into the answer field in
tblAnswers. You will likely want an expanded design that will limit the answers appropriately
for each question.

You can then use a crosstab query to produce the desired result:

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfy","so so","unsatisfy")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


You can also produce a total count of the answers for each question (since some clients may not
answer all questions):

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #],
Count(tblAnswers.Answer) AS [Total Responses]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfied","so so","unsatisfied")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


Tom
___________________________________


Hi,All
I am developing a client feedback program. Users can feedback clients
base on defined questions by the program, and fill in the answers into
program. The following table structure I designed is recording questions and
client's answers.

ClientID: number
question1: text(answer is: satisfy, so so, unsatisfy)
question2: text(answer is: yes or no)
...
question10: text(answer is: good, commonly, bad)

I designd a form base on above table, and feedback 65 clients by the form.
The next step, I must generate the feedback report. But how can I statistic
the data?
I try to statistic each question. The SQL query sentence is:
SELECT Count(ClientID) AS subtotal, question1
FROM tblFeedback
GROUP BY question1;

The result is:
subtotal question1
2 so so
62 satisfy
1 unsatisfy

But if I add qustion2 into above SQL query sentence:
SELECT Count(ClientID) AS subtotal, question1, question2
FROM tblFeedback
GROUP BY question1, question2;

The result is:
subtotal question1 question2
1 unsatisfy unsatisfy
2 unsatisfy satisfy
40 satisfy satisfy
5 satisfy so so
15 so so satisfy
2 so so so so

This result is NOT what I need! The result I expected is:
satisfy so so unsatisfy
question1 62 2 1
question2 62 1 3
question3 54 7 4
...
question10 57 7 1

Would you tell me how to do it and get above result? Thank you very much!!!
Click to expand...
 
T

Tom Wickerath

You're welcome!

Tom
_________________________________________


Thank you very much!!!

_________________________________________

Tom Wickerath said:
Hi Terry,

Your table design is not correct. You don't want a separate field for each question. You have a
many-to-many (M:N) relationship between clients and questions. A M:N relationship requires three
tables and is modeled by using two one-to-many (1:M) relationships, with the many side of each
relationship (ie. the "foreign keys") in a linking table. (Other names that I have seen for the
linking table include "Intersection" and "Join" tables).

tblClients
pkClientID <---Primary Key
ClientFirstName
ClientLastName
etc. (any other attributes about the client)

tblQuestions
pkQuestionID <---Primary Key
Question

tblAnswers (intersection table)
pkAnswerID <---Primary Key
fkClientID (foreign key)
fkQuestionID (foreign key)
Answer

After creating these three tables, populate tblClients with your client information and
tblQuestions with your questions. This is the minimum design, where one would type in the answer
(satisfy, so so, unsatisfy, yes, no, good, commonly, bad, etc.) into the answer field in
tblAnswers. You will likely want an expanded design that will limit the answers appropriately
for each question.

You can then use a crosstab query to produce the desired result:

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfy","so so","unsatisfy")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


You can also produce a total count of the answers for each question (since some clients may not
answer all questions):

TRANSFORM Count(tblAnswers.Answer) AS CountOfAnswer
SELECT tblQuestions.pkQuestionID AS [Question #],
Count(tblAnswers.Answer) AS [Total Responses]
FROM tblQuestions
INNER JOIN (tblClients INNER JOIN tblAnswers ON tblClients.pkClientID = tblAnswers.fkClientID)
ON tblQuestions.pkQuestionID = tblAnswers.fkQuestionID
WHERE (((tblAnswers.Answer) In ("satisfied","so so","unsatisfied")))
GROUP BY tblQuestions.pkQuestionID
ORDER BY tblQuestions.pkQuestionID
PIVOT tblAnswers.Answer;


Tom
___________________________________


Hi,All
I am developing a client feedback program. Users can feedback clients
base on defined questions by the program, and fill in the answers into
program. The following table structure I designed is recording questions and
client's answers.

ClientID: number
question1: text(answer is: satisfy, so so, unsatisfy)
question2: text(answer is: yes or no)
...
question10: text(answer is: good, commonly, bad)

I designd a form base on above table, and feedback 65 clients by the form.
The next step, I must generate the feedback report. But how can I statistic
the data?
I try to statistic each question. The SQL query sentence is:
SELECT Count(ClientID) AS subtotal, question1
FROM tblFeedback
GROUP BY question1;

The result is:
subtotal question1
2 so so
62 satisfy
1 unsatisfy

But if I add qustion2 into above SQL query sentence:
SELECT Count(ClientID) AS subtotal, question1, question2
FROM tblFeedback
GROUP BY question1, question2;

The result is:
subtotal question1 question2
1 unsatisfy unsatisfy
2 unsatisfy satisfy
40 satisfy satisfy
5 satisfy so so
15 so so satisfy
2 so so so so

This result is NOT what I need! The result I expected is:
satisfy so so unsatisfy
question1 62 2 1
question2 62 1 3
question3 54 7 4
...
question10 57 7 1

Would you tell me how to do it and get above result? Thank you very much!!!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating GPA. 7
Group information from several worksheets into one worksheet 1
MsgBox with Two Questions 5
averaging checkboxes 6
Analyzing Results of a Survey Using Option Buttons 4
Named dynamic range, COUNTIF, and advanced filter 5
group by and count 1
How to design report to display like I need it to 0

Top