How to queryvery many conditions in VBA

[Jim]

I'm looking for a suggestion. As part of an ap, I have to find a set of
records with a very large number of conditions and am wondering if there is
a "best" or standard way to handle such a query.

There is a category ID field, and I need to select all the records that
match the category. But there can be anywhere from 1 to 100 or more category
IDs (don't know how many or which until runtime) whose records I need to
find. The result is used to dynamically update a control as a user interacts
with it, so needs to be as quick as possible.

I could use a loop and construct a WHERE clause and concantenate a bunch of
OR conditions, but that's seems like it may be too many/long or perhaps
really ineffecient. (WHERE ID=10 OR ID=12 OR ID=30 OR....)

Or would a long IN condition work or be better, like WHERE ID IN (ID1, ID2,
ID3, ID4....). Would there be any difference in execution?

Another idea is to code a loop where each iteration executes an append query
for each ID. But then I'm running up to 100 seperate queries, which also
raises effeciency issues.

Thanks much!

Jim

 

[Allen Browne]

The IN operator will suit you best.

Access only copes with a limited number of AND/OR conditions, so IN will be
more useful.

 

[Tom van Stiphout]

On Wed, 1 Jul 2009 06:45:32 -0600, "Jim" <gofor26.2@g(remove)mail.com>
wrote:

I don't fully understand. If those 1 to 100 categoryIDs are in a table
or can be queried for, you can simply write:
....where CategoryID in (select CategoryID from SomeTable)
or semantically equivalent you can use an inner join.

-Tom.
Microsoft Access MVP

 

[KARL DEWEY]

You can create a table that contains the category ID's and join in the query.

 

[John Spencer MVP]

If you can construct a WHERE condition to get the categories, then you should
be able to use a sub-query in the where clause of the main query.

SELECT *
FROM SomeTable
WHERE CategoryID in
(SELECT CategoryID
FROM SomeOTHERTable
WHERE some conditions are used to filter records)

That might be the most efficient way for you to do what you need in terms of
effort.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

I forget where this came from, but it was my understanding the IN () was
just syntactic sugar and that the query engine would convert an IN () to
a series of OR criteria. I believe the most optimization can be gained
by using a join to a table containing the criteria.

 

[David W. Fenton]

I don't fully understand. If those 1 to 100 categoryIDs are in a
table or can be queried for, you can simply write:
...where CategoryID in (select CategoryID from SomeTable)
or semantically equivalent you can use an inner join.

To elucidate that a bit:

1. the JOIN is preferable because IN clauses in certain situations
(especially NOT IN) can sometimes fail to utilize all the available
indexes

2. the IN clause will allow the main table result to be editable in
some situations in which the equivalent join might not.

 

[Allen Browne]

Try it, Bob.

I just tried a SQL statement with 200 values in the IN. Works fine.
You won't get 200 ORs working.

(Tested in A2003 SP3.)

 

[Bob Barrows]

But is that because Jet is putting those values into a temp table behind
the scenes and using a join? I've been told that SQL Server does this
when the IN() contains too many values (instead of transforming it into
a bunch of ORs as it does for smaller sets of values) and was wondering
if Jet does the same thing. Yes, trying to apply what one database
engine does to what another one does is risky. That's why I'm wording
this as a question rather than an assertion.

 

[David W. Fenton]

But is that because Jet is putting those values into a temp table
behind the scenes and using a join?

No, Jet doesn't do that.

 

[Bob Barrows]

No, Jet doesn't do that.

Is there someplace I can read more about this? It's not a life-or-death
issue for me so don't go to a lot of trouble trying to find something.
I'm just a little curious.

 

[vanderghast]

NOT IN(list) and NOT IN(SELECT ... ) behave differently, in Jet, when a NULL
is involved, so it is logical to assume that both expressions don't share
the same execution code, with Jet.







SELECT *
FROM tablename
WHERE NOT 1 IN( 2, null)


return no record in MS SQL Server, and all the records, in Jet.

Use

SELECT *
FROM tablename
WHERE NOT 1 IN( SELECT nullabaleField FROM table)


with table.nullableFied DOES having a record with a null,

and no record will be returned in both, MS SQL Server and JET.



Vanderghast, Access MVP

 

[Jim]

I just wonder about the overhead of creating a temporary table and then
removing it. But it's clever idea that I hadn't thought of.
Thanks.
Jim

 

[Jim]

Thanks. Great suggestion. That's what I'm going to do, I think. Fortunately
in this case, the set of categoriesID is user selected, so I can stick them
in the "IN (x,y,z)" portion simply as comma separated values.
Jim