create indexing table to search database?

**Lyle Fairfield** · Aug 1 '06, 02:05 PM

Re: create indexing table to search database?

vonclausowitz@g mail.com wrote:

Hi All,
>
I was thinking of creating a table in my database to index all words in
the database.
That way I can quickly search for one or more words and the index table
will return the
words and records I need.
>
For example the iTable would look like this:
>
iID = autonum field
iSourceTable = text field
iSourceRec = numeric field
iWord = text field
>
Whenever a new record is added the iTable gets filled with all the
words from text and memo fields
in that new record, so for example:
>
iID = 1
iSourceTable = B (meaning Biographies)
iSourceRec = 102 (record number in Biographies)
iWord = "Male"
>
and so on for all words.
>
Then when I want to search for example for the word HOBBY, the iTable
will get all
records containing iSourceTable = "B" and iWord = "HOBBY".
>
Does this make any sense or should I do this in another way?

It might make sense if there is some good reason for wanting to know
which words are in all fields. It might also be a big table.
I'd be inclined to add the words to a word table, have a table, field
table and a linking table. If you messed for a while you could probably
grab all the words from one of the MS Lexicon collections, if by
nothing else, brute force and auto-spell.
Suppose the first word is "Aardvark" and the first Table, Field record
is "Birds", "BirdName" and the first record of that table contains
Aardvark in the Birdname field. Then the linking table would contain
it's own id (1), the id of the Table and Record, in this case 1, and
the id of the word, in this case also 1 and the record number, in this
case 1 as well.
Of course, not everyone would have aardvark in a birds table.
We could use these tables and joins to find out a great deal about your
field values, but not about "near". On the other hand if you included a
position, say 23, then you would have something like a zip file, and
you wouldn't need your original tables at all.
Do you think this is getting ridiculous? Possibly you are right.

On the other hand I have never had to do this because if I want to
search on words contained or near, I store my data in word or text or
pdf documents and use indexing service, which has a built in utility
for searches such a aardvark near "earth pig"/ or young not sexy; the
power of indexing service (once learned) is truly amazing.

**Mike Gramelspacher** · Aug 1 '06, 09:45 PM

Re: create indexing table to search database?

In article <1154423775.684 637.82870@s13g2 000cwa.googlegr oups.com>,
vonclausowitz@g mail.com says...

Hi All,
>
I was thinking of creating a table in my database to index all words in
the database.
That way I can quickly search for one or more words and the index table
will return the
words and records I need.
>
For example the iTable would look like this:
>
iID = autonum field
iSourceTable = text field
iSourceRec = numeric field
iWord = text field
>
Whenever a new record is added the iTable gets filled with all the
words from text and memo fields
in that new record, so for example:
>
iID = 1
iSourceTable = B (meaning Biographies)
iSourceRec = 102 (record number in Biographies)
iWord = "Male"
>
and so on for all words.
>
Then when I want to search for example for the word HOBBY, the iTable
will get all
records containing iSourceTable = "B" and iWord = "HOBBY".
>
Does this make any sense or should I do this in another way?
>
Regards
MArco
>
>

There is a routine by Ken Sheridan in another newsgroup for indexing
words. I used it on a memo field in an obituary database of 4,000
records. Some of these records are very short, a few sentences, and
others are hundreds of words long. This is a small database. Only
indexing words of 4 characters or more, I ended up with about 250,000
words. Searching based on the keyword list is indeed very fast,
however, not hardly noticeably faster than a wildcard search on these
few records. I had the cabability of searching keywords and fuzzy
(wildcard) searching, but dropped the keyword search as unneeded.
Danny Lesandrini offered a routine called Simple Search to search all
text fields in a record. It finds records in the database with, for
example, "Marco" in any field. I am asked, does the name Marco appearing
anywhere in this church record database? Simple Search tells me.
Just some thoughts.

Mike Gramelspacher

**vonclausowitz@gmail.com** · Aug 2 '06, 04:45 PM

Re: create indexing table to search database?

Mike,

please where can I find this post from Sheridan?

Marco

Mike Gramelspacher schreef:

In article <1154423775.684 637.82870@s13g2 000cwa.googlegr oups.com>,
vonclausowitz@g mail.com says...

Hi All,

I was thinking of creating a table in my database to index all words in
the database.
That way I can quickly search for one or more words and the index table
will return the
words and records I need.

For example the iTable would look like this:

iID = autonum field
iSourceTable = text field
iSourceRec = numeric field
iWord = text field

Whenever a new record is added the iTable gets filled with all the
words from text and memo fields
in that new record, so for example:

iID = 1
iSourceTable = B (meaning Biographies)
iSourceRec = 102 (record number in Biographies)
iWord = "Male"

and so on for all words.

Then when I want to search for example for the word HOBBY, the iTable
will get all
records containing iSourceTable = "B" and iWord = "HOBBY".

Does this make any sense or should I do this in another way?

Regards
MArco

There is a routine by Ken Sheridan in another newsgroup for indexing
words. I used it on a memo field in an obituary database of 4,000
records. Some of these records are very short, a few sentences, and
others are hundreds of words long. This is a small database. Only
indexing words of 4 characters or more, I ended up with about 250,000
words. Searching based on the keyword list is indeed very fast,
however, not hardly noticeably faster than a wildcard search on these
few records. I had the cabability of searching keywords and fuzzy
(wildcard) searching, but dropped the keyword search as unneeded.
Danny Lesandrini offered a routine called Simple Search to search all
text fields in a record. It finds records in the database with, for
example, "Marco" in any field. I am asked, does the name Marco appearing
anywhere in this church record database? Simple Search tells me.
Just some thoughts.
>
Mike Gramelspacher

create indexing table to search database?

create indexing table to search database?

Comment

Comment

Comment