469,898 Members | 1,417 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

All I Need to Know is if Atleast 1 Matching Record Exists

I'm trying to determine if any matching records exist on a LIKE query
performing a partial match of last names to a remote back-end database in
the most efficient manner possible. LAN Traffic appears to be the
bottleneck. This column is already indexed, and all I'm trying to determine
is whether no records, or at least one record, exists. My solution is the
following:

SELECT COUNT(*) FROM
(SELECT TOP 1 FROM table WHERE LastName LIKE "*match*")

My belief is that this will stop after finding the first matching record,
and return either a 0 (no matching records) or a 1 (at least one matching
record).

Any suggestions for an even better solution in MS Access 2000?

Thanks!

*David*
Nov 13 '05 #1
1 2215
"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:Y7********************@comcast.com...
I'm trying to determine if any matching records exist on a LIKE query
performing a partial match of last names to a remote back-end database in
the most efficient manner possible. LAN Traffic appears to be the
bottleneck. This column is already indexed, and all I'm trying to determine
is whether no records, or at least one record, exists. My solution is the
following:

SELECT COUNT(*) FROM
(SELECT TOP 1 FROM table WHERE LastName LIKE "*match*")

My belief is that this will stop after finding the first matching record,
and return either a 0 (no matching records) or a 1 (at least one matching
record).

Any suggestions for an even better solution in MS Access 2000?


Seems to me that all you need is the sub-query. It either returns one row or
zero rows. The Count query seems superfluous for what you need to know.

Someone will correct me if I'm wrong, but I think that in order to take
advantage of an index the match criteria has to be exact or at least not have
the wildcard at the beginning. If that's true then your existing criteria is
forcing a table scan which is never going to perform very well.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
9 posts views Thread by Robert Schneider | last post: by
5 posts views Thread by Bob Weisenburger | last post: by
3 posts views Thread by Roy W. Andersen | last post: by
6 posts views Thread by andyalean1 | last post: by
1 post views Thread by Donald Calloway | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.