Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: SQL sub query in Access

    Im trying to retrieve the row that matches a particular value and then retrieve all the rows that are related to that value.

    My table (tblLookup) consists of the following fields:

    • startDate
    • oldCode
    • endDate
    • newCode


    Sample Data

    oldCode newCode ....
    AAA BBB
    BBB BBB
    BBB BBB
    BBB CCC
    CCC DDD
    ZZZ ZZZ
    PPP RRR
    RRR RRR

    For example, the table may already contain the data above and I want to look up all the relationships which are linked to AAA. Then the query should return all the following rows:

    oldCode newCode ....
    AAA BBB
    BBB BBB
    BBB BBB
    BBB CCC
    CCC DDD

    It also has to be able to achieve the same result if I had a oldCode anywhere with the relationship so BBB, CCC or DDD.

    I have managed to get back all relationships with the code BBB, but if the swap the oldCode for AAA, CCC, or DDD I am unable to return all the relationships above.

    Heres my query so far:

    Code:
    SELECT *
    FROM tblLookup
    WHERE oldCode IN(
    SELECT newCode FROM
    tblLookup WHERE newCode IN(
    SELECT newCode FROM tblLookup WHERE oldCode ="BBB"));
    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think you're going to need multiple queries to do this. One for each level of "relationship". How many levels would there be?
    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
    Jun 2008
    Posts
    2
    Thanks for your reply.

    Yes, I was thinking I may have to something like that.
    However, the number of levels is not defined it could range from 0 to n.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Then I think you might need to use VBA for this, unless one of the SQL gods here can come up with a solution.
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in practice, "n" is never infinite

    see Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Cool

    You should have a sample database in that link to play with
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StarTrekker
    You should have a sample database in that link to play with
    dude, it's right there

    there is also an easter egg in the source html --
    Code:
    insert into categories (name,parentid) values
     ( 'animal', null )
    ,( 'vegetable', null )
    ,( 'mineral', null )
    ,( 'doggie', 1 )
    ,( 'kittie', 1 ) ...
    of course, this is standard SQL, and access would barf on it

    but at least it gives you a start
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, I meant a direct example in Access, not one buried in some resources page that has a squillion links on it
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you mean a downloadable MDB?

    i can't be bothered

    i mean, sheesh, the example is simple enough, innit?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes a downloadable MDB! And no, it's not that simple -- the concept is simple but the implementation isn't. Access people don't tend to dive that deep into SQL without a bit of a nudge
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and why would i choose to do this for Access? it's such a marginal database

    besides, i gave the CREATE TABLE in the article, all the data is in the article (and the INSERT statement is in the source html)

    if "Access people don't tend to dive that deep into SQL" then maybe they shouldn't be attempting this type of application

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

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why do you even come to the Access section then Mr AccessHater?!!! ^^

    lol
    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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to help lift people out of the mire
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    r937 - Savior of SQL n00bs
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by r937
    to help lift people out of the mire
    You could help more by providing that MDB
    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
  •