INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Include rows for which no data exists

Include rows for which no data exists

(OP)
Hey and Hello,

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

table:dummy_responses
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 

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
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 

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:
code	ques_elem	response
007265	8	        0 

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.

RE: Include rows for which no data exists

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

CODE --> 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

RE: Include rows for which no data exists

(OP)
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:

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 

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

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 

Then a left join of that with the actual responses:

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 

gets me where I think I want to be:

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


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.

RE: Include rows for which no data exists

Quote (BigRed1212)

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 smile

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close