Include rows for which no data exists

[BigRed1212]

Hey and Hello,

Given a table of survey responses from two dummy responders (I have more than two):

table:dummy_responses
[pre]code ques_elem response
001669 1 3
001669 2 3
001669 3 3
001669 4 3
001669 5 3
001669 6 3
001669 7 3
001669 8 3
007265 1 2
007265 2 2
007265 3 2
007265 4 2
007265 5 2
007265 6 2
007265 7 2[/pre]

one can see that responder 001669 responded to all 8 question elements whereas responder 007265 only provided data for 7.

I also have a table that defines the possible responses:

table:ques_1
[pre]questext quesvalue
One-on-one 1
Group presentation 2
Online 3
Social media 4
Cell phone app 5
Email 6
Printed materials 7
Other 8[/pre]

So that we know for ques1 there are 8 response areas, ques2 has 6 I think and so on, I have a table for each question.

What I want is a query that essentially adds an additonal row to the first table that looks like:
[pre]code ques_elem response
007265 8 0[/pre]

The zero can be a null, I don't care, I just need 8 response records for each responder to any part of this question for a nice regular uniform layout. Gotta graph and present it.

select b.*
from ques1 a left join dummy_responses b
on a.quesvalue = b.ques_elem
where b.ques_elem is null

gets me nothing and I don't think it should.

In short, I think somehow I want to identify responders in dummy_responses that haven't provided data for each quesvalue (maybe they skipped 2 instead of 8), create a record with that queselem and a zero or null as a response for each of those, and then union that up with the actual responses to create my uniform set, but I don't see how to do it.

Thoughts, ideas, tips, suggestions, help?

Thanks.

 

[dhookom]

Do you have a table of unique responders? If so you could create a cartesian query
qcarRespondersQuestions

SELECT DISTINCT Code, Ques_elem
FROM tblResponders, tblQuestions

Then create a new query that selects from qcarRespondersQuestions LEFT JOIN on dummy_responses

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

One of the things I like about forums (and this one is no exception) is that surprisingly often, the mere act of articulating your thoughts is enough to create a post allows you to solve your own problem.

It occurred to me after I posted the above and went to lunch that if I did:

[pre]select
distinct a.code,
"1" as quesvalue

from
dummy_responses a

order by
1,
2

union

select
distinct a.code,
"2" as quesvalue

from
dummy_responses a

order by
1,
2

union

etc all the way down to 8[/pre]

that I could create a "reference" or "template" table that looked like:

[pre]code quesvalue
001669 1
001669 2
001669 3
001669 4
001669 5
001669 6
001669 7
001669 8
007265 1
007265 2
007265 3
007265 4
007265 5
007265 6
007265 7
007265 8[/pre]

Then a left join of that with the actual responses:

[pre]select
a.code,
a.quesvalue,
iif(isnull(b.response),0,b.response)

from ques1_template a left join dummy_responses b
on a.code= b.code and a.quesvalue= b.ques_elem[/pre]

gets me where I think I want to be:

[tt]code quesvalue Expr1002
001669 1 3
001669 2 3
001669 3 3
001669 4 3
001669 5 3
001669 6 3
001669 7 3
001669 8 3
007265 1 2
007265 2 2
007265 3 2
007265 4 2
007265 5 2
007265 6 2
007265 7 2
007265 8 0[/tt]

Note the zero.

So maybe that is one way to do the trick. Got another or a cleaner way?

Thanks for your help just being here to potentially read something. Ha.

EDIT: I think I actually did more or less what Duane suggested. Cool.

 

[Andrzejek]

articulating your thoughts is enough to create a post allows you to solve your own problem.

Explaining your problem to a baby or an infant does the same trick - helps you solve your issues. These little 'creatures' do not talk back and that's why they can help so much

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.