Using subquery returns fewer records

[Pat]

I have a query that has a WHERE clause that checks for values IN, and
I pass it a list of values, like so:

WHERE Table.Fld08 IN ("Value1", "Value2", ...)

Because of a recent change in those values, I thought it best to
create a table to manage the values, and use a subquery to get the
current list, so I changed the query to the following:

WHERE Table.Fld08 IN (SELECT ValueName FROM tblValues)

Now I'm only getting about half the records that I got with the
previous version. Any idea why this would be? Better yet, how to
resolve it?

 

[Dale Fye]

Only thing I can think of is that the values you entered in tblValues do not
exactly match the values that were in your value list from the first query
(spaces or other typos).

I would do somethinglike:

SELECT DISTINCT Table.Fld08, tblValues.ValueName
FROM Table LEFT JOIN tblValues
ON Table.fld08 = tblValues.ValueName

This query will show you all of the values in your Table which do not have
matching values in tblValues. You might be able to identify the differences.

HTH
Dale

 

[Pat]

Dale -

Thanks for the suggestion, but it didn't solve the problem. Your
sample query worked just fine, but it returned all of the rows in the
tblValues as matches to the distinct values in the other table. If it
makes any difference, the table containing FLD08 is an Oracle table,
which I'm linked to via ODBC. tblValues is an Access table that I'm
also linked to in a different Access DB (I have a separated front end
and back end). Any other ideas out there? TIAFAH! - Pat

 

[Pat]

Well, now I really feel stoopid! I realized this morning that since
the data was in a table, I could just do an inner join on the table
and get the desired result. Sho' 'nuff - changing that IN expression
to a join returned me the same number of records as I was getting with
my original IN list of values. Still, the mystery of why it didn't
work the other way remains...