Simple query w. subquery doesn't work

[Guest]

Hi,

I'm trying to do a query of the type:
select a,b from table1 where a in (select c from table 2 where table2.d =
'bla')

and this fails. The reason surely is that the table2.c variable is a list of
text values like this: 'text1', 'text2'

when I execute only the subquery, I get the correct list of values. As soon
as I integrate the subquery into the IN clause of the main query, the latter
returns no rows at all.

Why ?

Thanks for help
Balex

 

[John Vinson]

Hi,

I'm trying to do a query of the type:
select a,b from table1 where a in (select c from table 2 where table2.d =
'bla')

and this fails. The reason surely is that the table2.c variable is a list of
text values like this: 'text1', 'text2'

when I execute only the subquery, I get the correct list of values. As soon
as I integrate the subquery into the IN clause of the main query, the latter
returns no rows at all.

Why ?

Because you're returning a single text string from Table2. The fact
that this single text string is a non-atomic composite field value
with commas in it is of no help to Access - it treats the value c as a
single unitary text value.

Since you're violating the principle of atomicity, there are only two
moderately ugly getarounds: substringing and building SQL:

SELECT a, b FROM table1
WHERE InStr([a], DLookUp("[c]", "[table 2]", "[d] = 'bla'") > 0

or actually constructing your SQL string in VBA code.

John W. Vinson[MVP]

 

[Marshall Barton]

I'm trying to do a query of the type:
select a,b from table1 where a in (select c from table 2 where table2.d =
'bla')

and this fails. The reason surely is that the table2.c variable is a list of
text values like this: 'text1', 'text2'

when I execute only the subquery, I get the correct list of values. As soon
as I integrate the subquery into the IN clause of the main query, the latter
returns no rows at all.

The issue is exactly what you said it is. The IN operator
expects a list of single values, not a set of multiple
values.

You could use a different operator such as Like:
WHERE (select c from table 2 where table2.d = 'bla')
LIKE "*" & a & "*"
But efficiency is out the window with an non-normalized,
multiple valued field in table2.

 

[Guest]

Hi,

I'm trying to do a query of the type:
select a,b from table1 where a in (select c from table 2 where table2.d =
'bla')

and this fails. The reason surely is that the table2.c variable is a list of
text values like this: 'text1', 'text2'

when I execute only the subquery, I get the correct list of values. As soon
as I integrate the subquery into the IN clause of the main query, the latter
returns no rows at all.

Why ?

Because you're returning a single text string from Table2. The fact
that this single text string is a non-atomic composite field value
with commas in it is of no help to Access - it treats the value c as a
single unitary text value.

Since you're violating the principle of atomicity, there are only two
moderately ugly getarounds: substringing and building SQL:

SELECT a, b FROM table1
WHERE InStr([a], DLookUp("[c]", "[table 2]", "[d] = 'bla'") > 0

or actually constructing your SQL string in VBA code.

John W. Vinson[MVP]

Hi John,

You know, the irony is that, if you look at the first post after mine in the
group, we both replied to it, mentioning to Matt that his table was wrongly
designed, because he had many values (asset numbers) in the same row for one
piece of work.

Well, I had basically done the SAME mistake, although I DO know a great deal
about DB design (but not so much about Access SQL, I'm rather working with
SQLServer and VBScript in the Web sphere) ! So, after reading your answer, I
changed my table key, just made as many rows in table2 as there were text
values, and presto, my original query works... So that's an even better (and
100 times simpler) solution than what you suggested, which would of course
had worked, but as I am my own master when it comes to the design of these
tables, I chose redesign against "moderately ugly workarounds".

Funny you did not think of that solution, just after having suggested a
similar one to Matt...

Anyway, thanks a lot, you helped me solving my problem !

Regards
Balex

 

[Marshall Barton]

Balex wrote:
[]

Funny you did not think of that solution, just after having suggested a
similar one to Matt...

I don't think that's fair Balex. Both John and I noted the
fundamental problem with your un-normalized data and since
you appeared to understand those issues (as demonstrated in
Matt's thread), didn't think you needed a tutorial in how to
redesign your data structure. I, and I assume John, felt
that you were somehow stuck with your structure and needed a
workaround, regardless of how ugly it was.