Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: Classifying Free Text field with Key Workds in a Lookup Table

    I am currently classifying a Free Text field by using Like "*KEYWORD*" in a Free Text field. This is causing a complicated hard to maintain set of IIF Statements.

    Is there a way to use a look up table that won't cause multiple records? Something like:

    KEYWORD1 TYPE1
    KEYWORD2 TYPE1
    KEYWORD3 TYPE2

    Any help or guidance would be greatly appreciated.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry, but I have no idea what you are asking.

    I am currently classifying a Free Text field by using Like "*KEYWORD*" in a Free Text field.
    Define "classifying" please.
    What is a "non free" text field?

    Any text field can be searched with wildcards such as Like "*KEYWORD*".

    This is causing a complicated hard to maintain set of IIF Statements.
    Why?

    Is there a way to use a look up table that won't cause multiple records?
    Use the lookup table WHERE? A Form? Report? Query? Code?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm with Trekky, but I have a hunch you want to either use DISTINCT or a correlated subquery using EXISTS.

    Note the former is likely less efficient than the latter.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2006
    Posts
    47

    More clear example...

    Thank you for the response and I apologize for not being clear.

    I have a table with a Free Text Fied like below:

    Text Field
    blah blah one blah blah blah
    two blah blah blah
    blah one blah two blah
    blah blah three

    Currently I us the following to classify the Type using the Field

    iif([Text Field] Is Like "*one*","One",iif([Text Field] Is Like "*two*","Two",iif([Text Field] Is Like "*three*","Three","Other")))

    This is a pain to maintain and ends up in a bunch of queries so when ever there is a change I have to find all of the queries containing this statement and correct it.

    What would be prefered is a way to have a table like the following

    Keyword Type
    one One
    two Two
    three Three

    Thus I can make changes to the above once. Additionally, it would be a lot easier to update the legend.

    As an additional note, there are times where I would want there to be an order (i.e. once it is type One it can't be type Two also) and times when I would want to classify all of its types.

    Again, thank you for your replies and any additional guidance would be greatly appreciated.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yep - my response stands.

    This article only confirms that what you are doing is right but in case you are interested:
    http://weblogs.sqlteam.com/jeffs/arc...2/10/9002.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rich8008
    Is there a way to use a look up table that won't cause multiple records?
    what you should be doing is creating an additional table that [b[does have[/b] multiple rows -- one row per keyword per type

    you will be quite please with how this simplifies your sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed.

    You might also like to consider making a global function to keep such an edit to one place.

    IE instead of:

    =IIf([Text Field] Is Like "*one*","One",iif([Text Field] Is Like "*two*","Two",iif([Text Field] Is Like "*three*","Three","Other")))

    Use

    =Categorise(strTextField)

    Do the IFing in your Categorise function, which returns the category string.

    Depends on your situation (how many records and where the output is used) as to whether this is successful or not, it will make it so you only have to edit the logic once with no table modifications, but it could also cause significant performance issues.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •