Ideas for a strange query in Access...

[nowanda]

Hello!

I have a funny concatenation problem...
My data is spread out as follows : Each person is asked 3 questions. I need to figure out the number of people who answered yes to the different questions. If they said Yes to more than one question then they need to be grouped (concatenated) and summarized...
# = person
A, B, or C are the types of questions
Y or N are the only possible answers

# Question Answer
1 A Y
1 B Y
3 C N
2 A N
2 B Y
2 C Y
3 A Y
3 B N
3 C N
4 A Y
4 B Y
4 C N
5 A Y
5 B Y
5 C Y

This is what I'm looking for:
Category Y
A 1
A + B 2
B + C 1
A + B + C 1

For example - person 1 and 4 both answered Y to questions A and B.

So somehow I'm concatenating the questions together where the answer is Y and I'm counting the number of Y's as well..

Nothing is impossible!! But how can I do this??

Nowanda

 

[Russell Hauf]

This is what I'm looking for:
Category Y
A 1
A + B 2
B + C 1
A + B + C 1

For example - person 1 and 4 both answered Y to questions A and B.

Sorry, I'm not following? How does the above show that person 1 and 4 both answered Y to questions A and B? And didn't person 5 also answer yes to both questions? Could you give a more specific example as to what you'd like to see?

Thank you,

Russell

 

[nowanda]

Sorry about that!

It certainly didn't come out as I intended.. Check this out and see if it makes any more sense...

The Person designates the same person - i.e. person 1 = Susan, Person 2 = Richard... etc.

The Question is one of three questions asked i.e.
A = Do you have an account with us
B = Do you have more than 10,000$ in investments
C = Do you have a mortgage

The answers are limited to Y or N.

I'd like to see all the people who :

A - Only Have an account
B - Only Have > 10 G invested
C - Only Have a mortgage
A+B - Have an account and have > 10G in inv
B+C - Have > 10 G and Have a mortgage
A+C - Have an account and Have a mortgage
A+B+C - Have all three

Book1
	A	B	C	D	E	F
1	ThisiswhatIhave:
2	Person	Question	Answer
3	1	A	Y
4	1	B	Y		ThisiswhatI'mlookingfor:
5	1	C	N		Question	#ofYAnswers
6	2	A	N		A	1
7	2	B	Y		A,B	2
8	2	C	Y		B,C	1
9	3	A	Y		A,B,C	1
10	3	B	N
11	3	C	N
12	4	A	Y
13	4	B	Y
14	4	C	N
15	5	A	Y
16	5	B	Y
17	5	C	Y
Sheet1

I've tried a few crosstabs - and I'm working on a script to try to compare some values.. I hope this clears things up a bit...

Nowanda

 

[Russell Hauf]

Ok, how about this:

1) Create a crosstab query -

Row Heading: Person
Col Heading: Question
Value: Answer (you can use First, Last, Min or Max - no difference).

Save the query.

2) Make a new query based on this query -

In the first field, put this in for the field:

Answers: [A] & & [C]

in the second field, put Person.

Then hit the "Totals" button (for a group by query). Group by the first field, and Count the second field.

You come up with something like this:<table BORDER="1" BGCOLOR="#ffffff" CELLSPACING="0"><font FACE="Arial" COLOR="#000000"><caption> </caption></font><thead><tr><th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Answer</font></th><th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">CountOfPerson</font></th></tr></thead><tbody><tr VALIGN="TOP"><td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000">NYY</font></td><td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000">1</font></td></tr><tr VALIGN="TOP"><td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000">YNN</font></td><td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000">1</font></td></tr><tr VALIGN="TOP"><td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000">YYN</font></td><td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000">2</font></td></tr><tr VALIGN="TOP"><td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000">YYY</font></td><td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000">1</font></td></tr></tbody><tfoot></tfoot></table>
.
It doesn't look exactly like you want it to, but you can get the information from it. You could make another table something like this:<table width="188" border="1"><col width=64 style='width:48pt'><col width=74 style='mso-width-source:userset;mso-width-alt:2706;width:56pt'><tr height=17 style='height:12.75pt'><td height=17 class=xl24 width=64 style='height: 12.75pt'>YNN</td><td class=xl24 width=111 style='border-left-style: none; border-left-width: medium'>A</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">NYN</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">B</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">NNY</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">C</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">YYN</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">AB</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">YNY</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">AC</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">NYY</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">BC</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">YYY</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">ABC</td></tr><tr height=17 style='height:12.75pt'><td height=17 class=xl24 style='height: 12.75pt; border-top-style: none; border-top-width: medium' width="64">NNN</td><td class=xl24 style='border-left-style: none; border-left-width: medium; border-top-style: none; border-top-width: medium' width="111">NO TO ALL</td></tr></table>

To make it how you like it (so YNN would be someone who answered yes to question A and no to questions B and C).

Hope this helps!

Russell
This message was edited by Russell Hauf on 2002-12-06 17:56
This message was edited by Russell Hauf on 2002-12-06 17:57

 

[nowanda]

Looks really cool!

It will definatley do the job!

Thanks again !

Nowanda