Search Question

[Jerry Spivey]

Hi,

Does Access 2003 have a full text search capability? I.e., can I enter a
few words and perform a search so that Access will bring back all records
that contain any of the words entered or possibly all of the words entered?

Thanks

Jerry

 

[Albert D.Kallal]

You can build expressions that search text.

So, in a sense ms-access can search text.

However, in database land, there is a term called "text indexing", and that
useally refers to the data engine's ability to "index" each word in some
text, and thus a text seach is very fast. (when you use JET data enigne with
ms-access, text "keyword indexing is not supproted.).

While a file of 50,000 to 75,000 is really very small, and nothing for
ms-access/jet, if you need complex text However, while this is

 

[Albert D.Kallal]

bumped send key...lets try again...


You can build expressions that search text.

So, in a sense ms-access can search text.

However, in database land, there is a term called "text indexing", and that
usually refers to the data engine's ability to "index" each word in some
text, and thus a text search is very fast. (when you use JET data engine
with
ms-access, text "keyword indexing is not supported.).

While a file of 50,000 to 75,000 is really very small, and nothing for
ms-access/jet, if you need complex text.

However, while this is a small file, if you need to search a large amount
of text, then performance will be a problem.

It really depends on how large your table/data sets are going to be.

select * from tblBooks where description is like *great* and like *romance*

The above will thus search for the key word "great" and also "romance" in
the description box for that table.

However, looking for *eat* will also match *great*.

So, JET does not support a key indexing system here. I could argue that it
would not take that long...

 

[Jerry Spivey]

Thanks Albert,

I was comparing Access 2003 vs VB.NET and SQL 2000 (w/ fulltext). Columns
will be < 5000 characters - rows will be < 1000 or so.

Still think Access 2003 would be OK?

Thanks

Jerry

 

[Guest]

Are you saying that you want to take text data stored in a field named TYPE
and display it as checkboxes on a form?
I think a better way would be to use an Option Group and store the data as
the Option Group number.

This SQL will give you the output to use in a form --
SELECT OPTIONSTable.NAME, IIf([TYPE]="PEER",-1,0) AS PEER, IIf([TYPE]="Group
of Peers",-1,0) AS [Group of Peers], IIf([TYPE]="OTHER",-1,0) AS OTHER,
IIf([TYPE]="SUPERVISOR",-1,0) AS SUPERVISOR
FROM OPTIONSTable;

In the form add checkboxes and edit the labels and source for each to match
the query output fields.

 

[Albert D.Kallal]

I was comparing Access 2003 vs VB.NET and SQL 2000 (w/ fulltext). Columns
will be < 5000 characters - rows will be < 1000 or so.

Still think Access 2003 would be OK?

Yes, that is not too bad. 5000 records is workable. As I mentioned, a table
of 75,000 records is small for ms-access, but when you start having to scan
each record for "text" search, you got a performance issue.

However, 5000 records is small enough here....

You could also spend a few hours, and write your own key word indexing. (you
would pull out the key words of the "memo", or "text" field, and then write
those key words out to a table with two columns:

recordID keyword


Searches on this "table" would be fast.