By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,329 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
"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.