473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 2384
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
5
by: yvan | last post by:
Approximately once a month, a client of ours sends us a bunch of comma-delimited text files which I have to clean up and then import into their MS SQL database. All last week, I was using a Cold...
9
by: Robert Schneider | last post by:
Hi to all, I don't understand that: I try to delete a record via JDBC. But I always get the error SQL7008 with the error code 3. It seems that this has something to do with journaling, since the...
1
by: Danny | last post by:
I am trying to loop through a database and if a keyfield exists in another table, I need to duplicate it. What do you recommend? I assume I need to link the two based on the key field (fields...
5
by: Bob Weisenburger | last post by:
I have a table of "memos". each record in that table has a primary key "memo ID" that is autonumber. I have another table that is "memo receipts" with each record having a "receipt id" field that...
3
by: Roy W. Andersen | last post by:
Hi, I need to do some replace-calls on certain strings in order to replace smiley glyphs and other keywords with graphical icons on the client. Unfortunately, my knowledge of regular expressions...
1
by: bpforte | last post by:
Hello, I need help with building query, basically I need to select all records from one table that don't exists in second table with status 1, but they can exists in second table with status 0, to...
6
by: andyalean1 | last post by:
Hello, I am trying to match an Id number that is shared across 2 xml files.I seem to be do the right thing bu it won`t display a match.Can you please help me find my error. I have a problem matching...
1
by: Donald Calloway | last post by:
I have created a database application that uses a macro, activated by a button click event, that runs a query which returns a recordset consisting of a subset of the matching database record, and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.