+ Reply to Thread
Results 1 to 11 of 11

Finding strings from 1 table in another table

  1. #1
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Finding strings from 1 table in another table

    Hello all - I'm going to start out with an apology for my general db and access ignorance. I've been kind of an excel enthusiast for the past year or so, but am now working with larger data sets that I've read are better handled by Access... so, figured I'd give it a shot.

    If anybody here would be kind enough to give me a little bump to get me started, I'd appreciate it.

    I have a database set up with two tables. Table 1 contains 4 columns of data and 900,000 records:
    Table 1
    A) Ticker symbol
    B) Ticker official name
    C) Ticker description
    D) <<Blank>>

    Table 2 is going to be about 500 records in two columns
    A) Keyword
    B) Keyword Category

    What I need to do seems to be fairly simple, but I don't know where to start. My objective is to do the following
    For each keyword in Table 2 (Column A: Keyword), search through Table 1 (Column B: Ticker Name). For every instance where the keyword is found, add the value from Table 2 (Column B: Keyword Category) to the record.

    To illustrate, if my Keyword is "Exchange Rate" and the Keyword Category is "Fixed Income", then I want to sweep through the NAME column in table 1 and add "FIXED INCOME" to anything that has "Exchange Rate" anywhere in it's name.

    Hope that makes sense... any guidance would be fantastic! And go easy on me, this is my first database!

    Thanks...
    JP
    Last edited by JP Romano; 01-11-2011 at 01:31 PM. Reason: removing

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Finding strings from 1 table in another table

    Would you clarify for me table 2? You have two fields with about 500 records. Are the 500 records unique? Are there are 500 different keywords? How many different keyword categories?

    I am trying to determine if this can be achieved through an update query or if you need to loop through your recordset with some VBA to update your table.

    Are you able to post your database without any proprietary data? Perhaps a scaled down version as there is a size limitation on posting.

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Finding strings from 1 table in another table

    Thanks so much for the reply...
    I'll set up a mockup with a subset of dummy data... but to answer your questions, the keywords will all be unique, yes, and there will be a limited number of categories (I believe when all is said and done, there will be 8). I'll create something without proprietary data and upload it...

  4. #4
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Finding strings from 1 table in another table

    Okay, here's the attachment...it's a small subset of data, but should be sufficient. Let me know if you have any questions or if I can clarify anything.
    Thanks!

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Finding strings from 1 table in another table

    JP;
    There is no attachment posted.

  6. #6
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Finding strings from 1 table in another table

    looks like I pulled the trigger too soon... this time it should be there (I hope)

  7. #7
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Finding strings from 1 table in another table

    I have no idea why the attachment isn't coming through...

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding strings from 1 table in another table

    What format is the file.... we can't download .mdb files...

    Try zipping the file.. we accept .zip
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Finding strings from 1 table in another table

    I think you have to zip it first for access files.

    If you continue to have a problem, click on the report icon in the upper right hand corner and advise the MODS.

    Alan

  10. #10
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Finding strings from 1 table in another table

    Won't upload... it's a zip file, below the 9.77 mb limit, but I just can't get it to work. Thanks for your time anyway...

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding strings from 1 table in another table

    Send a PM to our admin, RoyUk... to see why you can't include attachments.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1