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

    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

    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

    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.

    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

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


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

  2. #2
    Join Date
    Nov 2004
    out on a limb
    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