Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2006
    Posts
    11

    Red face Unanswered: Can I create a formula to search for and return a value within memo field

    Using Access 2000...

    Example:
    Table A has field with a list of users all within the same field in the row with no limit on # included:
    monkey.sanjose
    turtle.richmond
    bird.fredericksburg

    Table B has field with a single user listed per row (the table lists all users) without the fully qualified name:
    monkey (row 1)
    turtle (row 2)
    bird (row 3)

    I want to create a formula to search for the value of the field in table b within the value of the field in table a and then match up to the data in table b regarding that individual user. HELP!!!!

    This is a huge table, so manually fixing this is not an option.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Are all of the users in table A going to be in the same format? Ie: last.first? If so, you could use InStr and Mid or left to do a text comparison...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    11
    Unfortunately, no. Table A has fully qualified usernames monkey.richmond.corporate
    turtle.sanjose.ca
    bird.fredericksburg.system.corp

    and Table B has a list of the users but it's just the part before the . (period) - monkey
    turtle
    bird

    and there's no particular # of characters in the long names. the only commonality is the periods, but some users in the field have 2, some have 3, etc. I don't think there are ever more than 3 or 4 though.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Sounds like a possible design issue here....
    Also - is this a 1:1 or 1:m relationship?

    Anyway - simple SQL would be:
    Code:
    SELECT A.UserFQ, B.UserUQ 
    FROM TableA A, TableB B
    WHERE A.UserFQ LIKE B.UserUQ & ".*"
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2006
    Posts
    56
    I would do a string compare while loop through the recordset. using the instr function would be your best bet.

    Does table A have a record id ? if not, create one by setting up a new table and append the data from table a into a new table.

    in table b, add a record_id field. then do your string compare. When you find a match, then update table b with the record id from new table a.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by John_Farren
    I would do a string compare while loop through the recordset. using the instr function would be your best bet.
    Good lord - did I miss something in the original requirements? If not, surely that is one of the least optimal of all the possible solutions. Set based problems rarely gain from non-set based solutions.

    Gotta agree with you on the changes to the table although I can't help but feel that there is a deeper design problem here that surrogates will only hide. Having said that, they would be a step in the right direction.

    BTW turnertodd - an aside but - what is the longest text length in your memo field? If there are "never more than 3 or 4" then memo seems a tad excessive...
    Last edited by pootle flump; 02-05-06 at 08:45.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Posts
    11

    Smile thanks everybody

    It's really just a dataset that I need to pull the info from - only have to do it once and then use that as a table (without that awful memo field) in the new database. Thanks so much for your help - I was finally able to run it by using pootle flump's suggestion - although it took an age to run! I'm going to have to run it again to return subsets of the data though - the result of the query was too large to export!

  8. #8
    Join Date
    Feb 2006
    Posts
    56
    Pootle is absolutely correct. Next time I will drink my coffee before I reply, not after. Just curious, could you use a select distinct statement to cut down on the size of your query results?

Posting Permissions

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