Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: please oh please help! Dlookup? Maybe another way?

    Greetings,

    I am trying to look up a value from one table that matches a subset of a field in another table. How on earth would I go about that?

    I have two tables, one called "people" in this table I have a field called "famous_for" where an example data element is "she is a famous singer from Ireland"


    The other table is called "categories" - in this table I have a field called "Category" wher there is a list of categories like "singer", "writer", "athlete", etc.

    So in my example, I want Access to do this:

    Look at the "famous_for" field and see if any of the "Category" fields are contained in it.

    So when it sees "she is a famous singer from Ireland" - it returns the word "SINGER" as a match.

    Anyone have any clue how to do this????

    Alan

  2. #2
    Join Date
    May 2009
    Posts
    258
    You probably won't be able to do this with DLookup unless you set up a JOIN query first. If you are going to do it though, you might as well just use a query:
    Code:
    SELECT * FROM people A
    INNER JOIN categories B
    ON A.famous_for LIKE '*' & B.Category & '*'
    Ax

  3. #3
    Join Date
    Jul 2009
    Posts
    3

    that might work...if I knew how to do that :)

    Not sure how to implement an SQL phrase...sorry, maybe this is over my head.

    Quote Originally Posted by Ax238
    You probably won't be able to do this with DLookup unless you set up a JOIN query first. If you are going to do it though, you might as well just use a query:
    Code:
    SELECT * FROM people A
    INNER JOIN categories B
    ON A.famous_for LIKE '*' & B.Category & '*'
    Ax

  4. #4
    Join Date
    Jul 2009
    Posts
    3

    Wow...no way...I think I got it!!

    I gave it a try a couple of times, and your code is perfect!

    THANK YOU!!! I think it worked. Will verify later!

    You are the MAHN!

    Quote Originally Posted by Ax238
    You probably won't be able to do this with DLookup unless you set up a JOIN query first. If you are going to do it though, you might as well just use a query:
    Code:
    SELECT * FROM people A
    INNER JOIN categories B
    ON A.famous_for LIKE '*' & B.Category & '*'
    Ax

  5. #5
    Join Date
    May 2009
    Posts
    258
    Good going! Glad it helped.

Posting Permissions

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