Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Include rows for which no data exists

Status
Not open for further replies.

BigRed1212

Technical User
Mar 11, 2008
550
US
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.
 
Do you have a table of unique responders? If so you could create a cartesian query
qcarRespondersQuestions

SQL:
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
 
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.
 
BigRed1212 said:
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 [pc2] :)

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top