vo***********@gmail.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.