query challenge

alexxx · January 26th, 2006, 05:57 PM

Hi everybody,

I believe this is a challenging query:(at least for me)

can somebody give me some advice on the following:
I need to get the following data
this needs to end up in:

column 1 2 3
    questionnr. ID answer
        1 3 alex
        2 3 yes
        3 3 no
        4 3 maybe
        1 6 joanna
        2 6 no
        3 6 yes
        4 6 maybe

  How can I get it into a tabel like this????

column questionnr user1 user2 answerusr1 answerusr2
              2 alex joanna yes no
              3 alex joanna no yes
              4 alex joanna maybe maybe

great respect for the one that can help me out.

BrianWren · January 27th, 2006, 02:37 PM

Could you explain a little more?
If you add the following to your post (but without the spaces) [ c o d e ], WROXâs site will add what you type after that in non-proportional font, so that columns will line up. At the end of what you want to be non-proportional, put (without the spaces): [ / c o d e ]
The result:

Code:

column 1         2      3            
    questionnr.  ID    answer     
        1         3     alex     
        2         3      yes
        3         3      no
        4         3      maybe
        1         6     joanna     
        2         6      no
        3         6      yes
        4         6      maybe

column   questionnr   user1   user2  answerusr1  answerusr2
              2       alex   joanna     yes          no
              3       alex   joanna     no          yes
              4       alex   joanna     maybe       maybe

But beyond that, I donât follow the question. What does the source look like? How is the output related to the source?

alexxx · January 27th, 2006, 06:15 PM

Thank you sir for the suggestion.
I don't use forum too much, but this time I really got to a dead end with figuring this one out.
this tabel results from:
survey 1
question 1 what is your name? alex
question 2 do you like forums? yes
question 3 etc
survey 2
question 1 what is your name? Joanna
question 2 do you like forums? no
question 3 etc

for comparison of the ansers of 2 persons I need to get the first table contents in the second format. there is no coding, just access tables, and a lot of queries (that I made) that don't seem to work.
appreciate your help.
brainbreaker isn't it?

kindler · January 27th, 2006, 06:42 PM

Here's something similar to your format, I don't see a way to get the user1, user2 columns out of the data you have. This will have the people's names be the first row in the table, over their answers, which I think is more readable anyway.

SELECT DISTINCT Tbl1.questionnr, Tbl1.answer, Tbl1_1.answer
FROM Tbl1, Tbl1 As Tbl1_1
WHERE Tbl1.questionnr = Tbl1_1.questionnr AND Tbl1.ID <> Tbl1_1.ID
ORDER BY Tbl1.questionnr

alexxx · January 27th, 2006, 07:08 PM

checked and doublechecked
but in what kind of query must I put this? I tried the append query, and made de table owsResponseAnwser_1.
the error is "ms jet database engine can't find the owsResponseAnwser table or query"

SELECT DISTINCT owsResponseAnwser.QuestionID, owsResponseAnwser.Answer, owsResponseAnwser_1.Answer
FROM owsResponseAnwser, owsResponseAnwser AS owsResponseAnwser_1
WHERE owsResponseAnwser.QuestionID = owsResponseAnwser_1.QuestionID And owsResponseAnwser.ResponseID<>owsResponseAnwser.Re sponseID
ORDER BY owsResponseAnwser.QuestionID;

where did I go wrong?

DeMoNN · October 31st, 2006, 03:34 PM

i think i can help you but this topic is from january

if you still need it (heh) mesege me

I was here but now I'm gone.
I leave this messege to be carried on.
Whoever knew me, knew me well.
Whoever didn't, go to hell.