Selecting a field twice from the same table ?

[webmonkey]

I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.

Respondents includes
RID
Name
PostCode

Questions
QID
Question

Answers
AID
QID
RID
Answer

The answer table contains answers to all the different questions.

How can I query this to compare questions ? grouping answers to spot trends in the data

So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.

Any help would be much appreciated, or even a point in the right direction.
I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.

Thanks

 

[neileg]

Create two queries based on the Answers table. One pulls records where the answer to Q1 is Yes and the other pulls Q2 No. Join these two queries in a third query with the Respondant table making sure this is a left join so that you get all respondants. Add criteria to test the the data from the two sub queries is not null.

 

[webmonkey]

I shall try that out, thanks!

I think I was pulling all data in both queries then attempting to join, rather than just pulling the answers then adding the respondent info, which makes a lot of sense now you've suggested it.

Cheers

 

[webmonkey]

Hmm, I've given that a try and its still going horribly wrong - I ended up with600 results and there are only 8 respondents in the db at the moment.

Reading over what I wrote I'm not sure if i explained it properly.

I want to have the postcode from tblRespondents, the question for question 1, the answer for question 1, the question for question 2, the answer for question 2 and be able to group these somehow, a count function ?

PCode | Q1 | Answer1 | Q2 | Answer2 | Count |

So i could see that all people with postcode MDB say yes to q1 and no to q2, or 50% of people with a postcode D99 say yes to both.

Thanks

 

[neileg]

You've got a cartesian product. You need to create a join between the respondant table and the two queries on the respondant ID. As I said, this needs to be a left join.

 

[GaryPanic]

how about (coming in on this I am not good at multly qry )
have your form have the questions yes/no and select the answer set combo box
which would be yes/no
and run either an update qry to a third field = q1=a1 then markbox = 1 else 0
or do this on a afterupdate on a combo box for the answers
and you could then tally thee up and do % all on one click

Perhapss ????

 

[GaryPanic]

the count bit should be easy if Q=a then count =1
else 0

 

[webmonkey]

You've got a cartesian product. You need to create a join between the respondant table and the two queries on the respondant ID. As I said, this needs to be a left join.

Thanks!

I've done it without the left join, for now, if they haven't answered the question not much point listing it.

I think I was trying to count the wrong field, now I'm counting RID instead it all appears to work.

Cheers!

Gary - Not entirely sure I understand but these aren't all Y/N answers anyway, there's a combination of every answer type you can think of. Thanks anyway though.

 

[neileg]

OK. Pleased it's working.