Apply constraint before left join

[Dave S.]

Windows 2000, Access 97

I've got this table (import to Access as comma delimited):

session_id,question_id
s1,q1
s1,q2
s1,q3
s2,q1
s2,q3
s3,q1
....

I want a query that produces this:
session_id,question1,question2,question3
s1,q1,q2,q3
s2,q1,,q3
s3,q1,,
....

Note that some sessions_ids do not have all of the
question_ids that others do.

Querying the table against itself with LEFT JOINs followed
by WHERE constraints with a question_id does not work as
the WHERE is implemented after the join and no records
that are missing that particular question_id are shown.
SQL subqueries don't work either as Access only seems to
allow them in the WHERE clause with no alias.

I know I could create seperate queries for each question
and then LEFT JOIN them all. However, the real tables
have hundreds of question_ids and would therefore result
in a very large number of queries to deal with (enough to
make human error risk unacceptable).

I'd also prefer to avoid VBA as these some of these
queries will have to be put together or at least
understood by non-developers -- a simple, clever SQL
structure or a few formulas woud be ideal.

Anybody out there know a simple way to make Access only
look at a part of a table before it does an outer join?
Or have any other suggestions?

Thanks very much in advance, I really appreciate it.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You talking about a LIST() function - Access (JET) doesn't have that
function in its SQL library. You will have to create it in VBA, or
you can search the Web for it. I've seen it somewhere. Try the
comp.databases.ms-access newsgroup using Google's groups search.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB68l4echKqOuFEgEQJrkwCfRYrWbC3owvFpVY3Ofk9ctqcVClEAoJFN
6b5PHNTDRNC3TKlo8/9IQCtS
=jtyV
-----END PGP SIGNATURE-----

Windows 2000, Access 97

I've got this table (import to Access as comma delimited):

session_id,question_id
s1,q1
s1,q2
s1,q3
s2,q1
s2,q3
s3,q1
...

I want a query that produces this:
session_id,question1,question2,question3
s1,q1,q2,q3
s2,q1,,q3
s3,q1,,
...

Note that some sessions_ids do not have all of the
question_ids that others do.

< SNIP >

 

[david epsom dot com dot au]

I don't even understand the question, but if you
do a crosstab query, you can supply a column list,
so you get records like:

session_id, quesion0, question1,question2,question3,
s1 1 1 1
s2 1 1

(david)

 

[Guest]

Thanks for that -- haven't found anything about LIST()
yet, but I'll keep looking. Can you tell me a bit about
what it does?

Sorry if my question's unclear -- basically, I want to do
what a simple LEFT JOIN is supposed to do, but it doesn't
work since I only need to join to part of the table. (NB:
I think that the join works the way I want in SQL Server.)

If you divide up the table below into one table for each
question and then join the tables with left joins, it's
perfect. But I'd *love* to do it all in one query without
all the extra tables/queries.

Thanks again,

Dave

 

[Guest]

Answered my own question, but in case anybody else is
interested:

What finally did the trick was including a subquery in the
front of the SELECT statement instead of in the WHERE or
FROM clauses (equivalent to putting it in the top row of
Access's design window). Entering the SQL code below
makes it work.

SELECT my_table.session_id, my_table.question_id,
my_table.answer, (SELECT my_table_1.answer FROM my_table
as my_table_1 WHERE (my_table_1.question_id="q3" AND
my_table.session_id=my_table_1.session_id)) AS Expr1
FROM my_table
WHERE (((my_table.question_id)="q1"));

Whew. Glad to have figured it out, but I'm still not
really sure why it works. Putting the subquery in the
FROM clause still makes a lot more sense to me. Any
academics still interested?

Anyway, thanks for the help everyone!

Dave S.