Many to Many Table/Cross Tab Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Many to Many Table/Cross Tab Query

Author

Topic

Nexzus
Starting Member

14 Posts

Posted - 2005-04-02 : 21:19:43

Need help with a Cross Tab query

Say I have three tables:

Survey
SurveyID (PRIMARY KEY)
Other Misc, unimportant stuff....
------

YesNoQuestion
QuestionID (PRIMARY KEY)
QuestionText
------

Survey_YesNoQuestion
SurveyID (COMPOSITE PRIM. KEY)
QuestionID (COMPOSITE PRIM. KEY)
------

Pretty simple how this is setup. An entry is made into Survey_YesNoQuestion if the corresponding question on that survey has been selected 'yes'

What I need is a list of all the SurveyID's on the left, the Question ID's as column headings, and then Yes/NO, 0/1, or anything boolean as values for the questions, obviously Yes/True/1 if there is an entry/response to that question is yes for that survey, and No/False/0 if there is no entry for that question for that survey.

Example.



Survey
SurveyID     OtherStuff....
---------------------------
Survey1      ...
Survey2      ...
Survey3      ...
---------------------------

YesNoQuestion
QuestionID    QuestionText
--------------------------
Question1     Do you...?
Question2     Have you...?
Question3     Will you...?
Question4     Can you...?
--------------------------

Survey_YesNoQuestion
SurveyID      QuestionID
-------------------------
Survey1       Question1
Survey1       Question4
Survey2       Question3
Survey3       Question2
Survey3       Question4
-------------------------

Query:

SurveyID    Question1     Question2     Question3     Question4
---------------------------------------------------------------
Survey1     TRUE          FALSE         FALSE         TRUE
Survey2     FALSE         FALSE         TRUE          FALSE
Survey3     FALSE         TRUE          FALSE         TRUE
---------------------------------------------------------------

I'm using an Access XP front end to a remote MS-SQL Server 2000 database

Thanks for your help.

edit:
I should note that this a very simplified example of the problem I'm working on. around 200 of the 'YesNoQuestions' for example. It's also not my design.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-02 : 21:48:36

Are you using an ADP file or an MDB file in Access? if you are using an MDB file, it has built-in support for cross-tab queries.

- Jeff

Nexzus
Starting Member

14 Posts

Posted - 2005-04-02 : 21:52:14

Jeff,

Thanks for your quick reply. Yeah, I'm just using a regular .mdb file, using Linked Tables to the server.
I know how to create a cross tab query in Access, I'm just not sure how to do this particular one.
Sorry for the confusion.

Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources