Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Unanswered: query based on a many-to-many relationship

    Hello. This is my first post here. I hope it makes since to all.

    Let's suppose we have a table DOCUMENTS with the fields DocTitle (primary key), Format, Topic like so

    DOCUMENTS
    DocTitle Format Topic
    ---------------------------
    A1 pdf Weather
    A2 doc Sports
    A3 doc News
    A4 pdf Foreign

    Then we have another table called USERS, that defines the people that created the documents.
    Each document can have many users and each user can be in many documents. Primary key is UserCode

    USERS
    UserCode Name
    ----------------------
    0001 John
    0002 Steve
    0003 Jack
    0004 Bill

    So, because of the many-to-many relationship we have another table, that defines which documents were created by which users. The key is both fields together.

    DOCUMENTS_USERS
    DocTitle UserCode
    ---------------------
    A1 0001
    A1 0002
    A1 0003
    A2 0001
    A2 0002
    A3 0001
    A3 0003

    These tables have the relationships
    DOCUMENTS.DocTitle -> DOCUMENTS_USERS.DocTitle (1-many)
    USERS.UserCode -> DOCUMENTS_USERS.UserCode (1-many)


    What I could like is a query that displays the following:

    DocTitle: A1
    Format : pdf
    Topic : Weather
    Users : John
    Steve
    Jack

    DocTitle: A2
    Format : pdf
    Topic : Weather
    Users : John
    Steve

    etc

    It may sound simple but I can't find the way. Please help, thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not sure you can do this in a query, but you can do this in your front end as a form or report

    in a form you could display the document as the primary form and embed a subform which which dispalys the users, or a combo box for each document

    in a report, create a single query which has all the data in, then push the document to a group header, the users to a a detail section
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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