Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    One-to-Many Relationship question

    I am using microsoft access(unfortunately I have no choice) to create queries.

    I have a main table that contains information about a book(one) and 2 secondary tables(many)

    Book Table
    ID,Title

    Keyword Table
    Keyword, foreign_key(ID from book table)

    Author Table
    Author Name, foreign_key(ID from book table)

    I want to do the following:

    Author,Keyword, Title, ID

    Say I have the data like this
    Book Table:
    1 Bob's Poker Tips
    2 Bob's Cookbook

    Keyword Table
    2 Baking
    2 Stewing
    1 Texas Holdem

    Author Table
    2 Joe Smith
    2 Bob Perini
    1 Bob Perini

    If I query the author Bob Perini

    I'm currently getting from my query:
    ID,AUTHOR,KEYWORD,TITLE
    1 Bob Perini Texas Holdem Bob's Poker Tips
    2 Bob Perini Baking Bob's Cookbook
    2 Bob Perini Stewing Bob's Cookbook

    I want to get the following:
    ID,AUTHOR,KEYWORD,TITLE
    1 Bob Perini Texas Holdem Bob's Poker Tips
    2 Bob Perini Baking,Stewing Bob's Cookbook

    If you don't know the syntax for the Jet SQL engine, then feel free to share the theory or show me a query in someone other sql.

    Thanks in advance

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    It sounds like you have a Microsoft Access question instead of a design question. Try posting in the Microsoft Access forum.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, it is (also) very much a design question

    this question comes up all the time, and the answer is:

    do it with a scripting language after you've applied an ORDER BY to the query to ensure the result rows come back in in the right order to allow current/previous logic

    i.e. don't try to do this with sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Location
    trenon, tn.
    Posts
    2
    are people cheating on line poker like 3 people playing at same table talking to each

Posting Permissions

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