Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 26th, 2006, 05:57 PM
Registered User
 
Join Date: Jan 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default query challenge

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.




 
Old January 27th, 2006, 02:37 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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?
 
Old January 27th, 2006, 06:15 PM
Registered User
 
Join Date: Jan 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old January 27th, 2006, 06:42 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 27th, 2006, 07:08 PM
Registered User
 
Join Date: Jan 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old October 31st, 2006, 03:34 PM
Authorized User
 
Join Date: Oct 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to DeMoNN
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
A little challenge... chacquard Access VBA 7 February 2nd, 2007 11:38 AM
Memobox Challenge rocknrisk Access 8 July 30th, 2005 02:36 AM
A Challenge... AForgue XSLT 3 November 5th, 2003 04:14 PM
Challenge.... developerz BOOK: ASP.NET Website Programming Problem-Design-Solution 2 September 29th, 2003 04:49 PM
Very tricky challenge 12th_Man SQL Language 3 June 20th, 2003 06:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.