Thanks to everyone who has responded to this. I still don't have an answer that
will work, so here is a bit more information:
1) The name fields are divided into 4 parts:
Prefix, GivenNames, Surname, Suffix
3) The GivenNames field already has more than one word in many instances.
Sample entries are:
Mary Elizabeth
J. W.
Marg. Ellen
4) I need a way to show alternate spellings when these very old documents are
difficult to read. Examples:
Example a (GivenNames): Mary Elizabeth or Marg Elizabeth
Example b (GivenNames): J. W. or I. W.
Example c (Surname): Stotts, Statts or Stutts
I need to be able to retrieve the following based upon the examples:
For Example a:
Return Mary Elizabeth where GivenNames begins with M;
Return Marg Elizabeth where GivenNames begins with M;
Return Mary Elizabeth where GivenNames contains the whole word Mary;
Return Marg Elizabeth where GivenNames contains the whole word Marg;
Return Mary Elizabeth where GivenNames=Mary Elizabeth
Return Marg Elizabeth where GivenNames=Marg Elizabeth
Example b:
Return J. W. where GivenNames begins with J;
Return I. W. where GivenNames begins with I.;
Return J. W. whre GivenNames=J. W.;
Return I. W. where GivenNames=I. W.
Example c:
Return statts where Surame=statts;
Return stotts where Surame=stotts;
Return stutts where Surname=stutts
This is a huge database so the option of using LIKE to bring up everything
beginning with the search term will result in too many hits. I need a way to
isolate these entries and search them on whole words.
I had considered using SET and creating a set of all unique surnames, but the
surname count for the set right now is over 120,000, so this exceeds the
maximums allowed for SET.
I've also considered using separate fields for 'surname alternate spelling 1',
'surname alternate spelling 2', etc. Is this the only way I can get the results
I need?
Thanks!
Ian Grant <[EMAIL PROTECTED]> wrote:
On Wed, 05 Jan 2005 12:22:18 +0000
Stephen Moretti wrote:
> Kentucky Families wrote:
>
> > ... If I use a VARCHAR or TINYEXT field to enter these values and
> >I want to be able to retrieve all records where the surname field
> >contains the whole word Stotts, how would I enter these values:
> >
> >stotts or statts or stutts
> >stotts,statts,stutts
> >other?
>
> You need to do an IN query.
>
> SELECT column,list,here
> FROM tblBMD
> WHERE Surname IN ('stotts','statts','stutts')
This will not match any of the records with multiple transcriptions.
I think you have mis-understood the question. As I understand it she
asks how to enter multiple possible transcriptions into the field so
that they can be retrieved easily.
My answer is: since commas are unlikely in names, that is as good a
separator as any. To do the query use wildcard matching with LIKE e.g.
SELECT * FROM table WHERE surname LIKE '%stotts%';
will match a field 'Stotts,Statts,Stutts' or just 'Stotts' but note it
will also match e.g. 'Stottsford,Stattsford,Stuttsford'
---------------------------------
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.
