Sorting information from foreign table

[Crogirl]

I'm not sure if this can even be done or maybe it is extremely simple and I missed something fundamental, but here goes...
I have a db with my main table and several other tables with information that I use as foreign keys in the main table. For example, my "gender" table has 3 options; male, female and no response. When I query this to extract # of responses by gender, I group by gender and count the number of responses to the various questions in the db. When I create a report w/ this query, the gender groupings form my rows, and my questions form my comments
Ex:
Q1 Q2 Q3 etc.. etc..
Female
Male
No Response

I have several questions, and there is no room on the report to list them all in landscape. Is there a way to make the gender groupings form my coulums and the questions form my rows?

Regards,

 

[ssecca]

Take a look at Crosstab queries. Should do what you want.

Good Luck
ssecca

 

[Crogirl]

Thanks, I tried that, but unfortunately I have too many questions going across for a crosstab

 

[johnnymac43]

You can use a crosstab query if you structure your data to enable the required fields to be accessed in the manner that the crosstab needs!

I was unsure from your post of your precise requirements, and thus have made the following assumptions:
> Where gender is not supplied the response to a non-gender question (if given) is counted under 'No Response'.
> Where no response is given to a non-gender question, nothing is counted for that individual for that question.

For the example I have structured the data as follows:
> tblMain contains an ID for each individual, and a link to the Gender table.
> tblGender is as defined by you.
> tblQuestion contains an ID for each non-gender question, and the question text.
> tblQuestionResponse contains the following for each question asked for each individual:
>> Response ID (autonumber)
>> tblMain ID for the individual
>> tblQuestion ID for the question
>> Response indicator (Yes/No) for the question
>> Response value

The SQL for the query (generated by Access) is shown below:

TRANSFORM Count(tblQuestionResponse.Response)
AS CountOfResponse
SELECT tblQuestion.Question
FROM (tblMain
INNER JOIN tblGender
ON tblMain.GenderID = tblGender.tblGenderID)
INNER JOIN (tblQuestionResponse
INNER JOIN tblQuestion
ON tblQuestionResponse.tblQuestionRef = tblQuestion.tblQuestionID)
ON tblMain.tblMainID = tblQuestionResponse.tblMainRef
WHERE (((tblQuestionResponse.Response)=Yes))
GROUP BY tblQuestion.Question, tblQuestionResponse.Response
PIVOT tblGender.Gender;

On my test file, with 4 individuals (2 male, 1 female, 1 no response) responding to 3 questions (with 2 null responses out of 12), this gives the following results:

Question Female Male No Response
Do you read books? 1 2
Do you watch movies? 1 2 1
Do you watch TV? 1 1 1

HTH

John

 

[Crogirl]

Thank you!