Converting Rows of Excel Data into Summary

[RJB]

I *think* this is an Access question. Been about ten years since I've
actually had to use Access myself, but I think I've landed in the right spot.

Received a report from Excel, a survey. Sixteen respondents, each row of the
report is one answer to one question from one respondent. In other words:

RESP | TOPIC | QUESTION | RESPONSE
=================================
Adam | Envir. | Quest. 1 | 4
Adam | Envir. | Quest. 2 | 3
Adam | Mt'ls. | Quest. 3 | 4
Adam | Mt'ls. | Quest. 4 | 4
Brian | Envir. | Quest. 1 | 5
Brian | Envir. | Quest. 2 | 4
Brian | Mt'ls. | Quest. 3 | 4
Brian | Mt'ls. | Quest. 4 | 3
.... and so on, to respondent 'n'.

I'd like a report that reads as follows:

ENVIR.
Quest. 1
A | 4
B | 5
....
n | 4
====
Avg. 4.3

Quest. 2
A | 3
B | 4
....
n | 4
===
Avg. 3.67

MAT'LS.
Quest. 3

etc. etc. etc.

I would have thought putting the survey into two Queries and linking might
work, but as it's organized in rows, I'm a wee bit lost.

I have over 500 lines of data, so if I can AVOID grinding it out by hand,
I'd LIKE to.

Thanks, folks!

rjb

 

[Fred]

Hello RJB

Make a report which groups:

First by Topic, "Group Header" = Yes

Next by Question, "Group Header" = Yes, Group Footer = Yes

Next by Resp (no group headers or footers)

Then:

In your "Topic" group header, put a box for topic
In your "Question" group header, put a box for question,
In your "detail" section, put boxes for "resp" and "response"

in your question group footer, put a text box with countrol source
=avg([Response])

Above is untested

Of course this assumes that "response" is a numerical value with numeric
meaning.

 

[Fred]

Hello RJB

Make a report which groups:

First by Topic, "Group Header" = Yes

Next by Question, "Group Header" = Yes, Group Footer = Yes

Next by Resp (no group headers or footers)

Then:

In your "Topic" group header, put a box for topic
In your "Question" group header, put a box for question,
In your "detail" section, put boxes for "resp" and "response"

in your question group footer, put a text box with countrol source
=avg([Response])

Above is untested

Of course this assumes that "response" is a numerical value with numeric
meaning.

 

[RJB]

Great stuff. Having some trouble still, though.

Is it possible to sort:
- By Respondent
- By Question
....given the above?

What it's doing is sorting by
- Topic
- Question
- Answer

In other words, the first answer is the First Topic in Alpha, then the first
Question in Alpha, then the lowest-numbered response. Then the next
lowest-numbered response, etc.

Additionally, I've discovered that the answer field is sometimes Number and
sometimes Alpha!

Yikes, stripes!

 

[RJB]

Great stuff. Having some trouble still, though.

Is it possible to sort:
- By Respondent
- By Question
....given the above?

What it's doing is sorting by
- Topic
- Question
- Answer

In other words, the first answer is the First Topic in Alpha, then the first
Question in Alpha, then the lowest-numbered response. Then the next
lowest-numbered response, etc.

Additionally, I've discovered that the answer field is sometimes Number and
sometimes Alpha!

Yikes, stripes!

 

[Fred]

Hello RJB,

Yes you can do that. Just put your desired fields into that box according
to the sorting heirarchy that you want. Of course, in order to group (or
summarize by group) for a criteria you have to sort by that criteria.

You communicated your desired report very clearly in your first post, and
what I gave you was based on / fufiled that. Your second post defined a
desired sort which is different than (i.e. conflicts with) your first post.
That's fine as long as you understand that that is the case...... you can
change your mind or make two different reports sorted/grouped two different
ways.

Regarding your "answer" field, your Access field type is either number or
text but not both. You probably have a text field which contains both
numeric and alphabetic characters. Averaging is a mathematical function
performed (only) on numbers. Access aside (Ignore Access for a moment)
.....you have to work out your intentions so that they do not call for
performing such an operation on text. And THEN implement your intentions
in Access.

Hope that helps a little.

 

[Fred]

Hello RJB,

Yes you can do that. Just put your desired fields into that box according
to the sorting heirarchy that you want. Of course, in order to group (or
summarize by group) for a criteria you have to sort by that criteria.

You communicated your desired report very clearly in your first post, and
what I gave you was based on / fufiled that. Your second post defined a
desired sort which is different than (i.e. conflicts with) your first post.
That's fine as long as you understand that that is the case...... you can
change your mind or make two different reports sorted/grouped two different
ways.

Regarding your "answer" field, your Access field type is either number or
text but not both. You probably have a text field which contains both
numeric and alphabetic characters. Averaging is a mathematical function
performed (only) on numbers. Access aside (Ignore Access for a moment)
.....you have to work out your intentions so that they do not call for
performing such an operation on text. And THEN implement your intentions
in Access.

Hope that helps a little.

 

[RJB]

Thanks.

I didn't change what I wanted... I may not have articulated as well as
possible!

I asked for a way for my report to print the answers in the order the
questions were asked, in the order of the the respondents.

What I got was a report with questions sorted by alpha, and answers sorted
by the content of the answer.

I will try again based on updated info.

 

[RJB]

Thanks.

I didn't change what I wanted... I may not have articulated as well as
possible!

I asked for a way for my report to print the answers in the order the
questions were asked, in the order of the the respondents.

What I got was a report with questions sorted by alpha, and answers sorted
by the content of the answer.

I will try again based on updated info.

 

[Fred]

Hello RJB,

Communication mixup.... I thought that you were saying that you no longer
wanted your top level sort to be Topic, as it was in your first post.

Sorry I also screwed up and forgot to say to add "resp" as the third level
sort.

- - -

Also, the thread didn't go into detail on items raised on your last post.

Sounds like you might want to make a separate table which is a list of your
questions. Including a PK (Primary key) "QuestionNumber" field, where the
number follows the order in which the questions are asked. (If that keeps
changing, the you'll need a little fancier structure.) then put the
QuestionNumber field into your main table and link the two QuestionNumber
fields. Then change your 2nd level sort/group to questionNumber
- - -

 

[Fred]

Hello RJB,

Communication mixup.... I thought that you were saying that you no longer
wanted your top level sort to be Topic, as it was in your first post.

Sorry I also screwed up and forgot to say to add "resp" as the third level
sort.

- - -

Also, the thread didn't go into detail on items raised on your last post.

Sounds like you might want to make a separate table which is a list of your
questions. Including a PK (Primary key) "QuestionNumber" field, where the
number follows the order in which the questions are asked. (If that keeps
changing, the you'll need a little fancier structure.) then put the
QuestionNumber field into your main table and link the two QuestionNumber
fields. Then change your 2nd level sort/group to questionNumber
- - -