Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Junction Table for Junction Tables (2007)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    My problem is similar to that in <post:=659,132>post 659,132</post:>. However, the attachment from that thread is no longer available.

    I believe I need a junction table for the relationships between two other junction tables. I'm not sure how to proceed.

    I have two parallel processes.

    Reports are submitted by Authors. There is a many-to-many relationship there, so I create a junction table ReportsAuthors with 2 primary keys, one from Reports and one from Authors.

    Reports are evaluated by Managers. The setup is also many-to-many, so I end up with a ReportsManagers junction table.

    Now, to tie everything together, I believe I need a ReportsAuthorsManagers junction table.

    In general, would this have 4 primary keys?

    In my case, should it have 3, since the Report_ID key will show up twice? If yes, how do I set this up?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Junction Table for Junction Tables (2007)

    Unless you need to relate specific managers to specific authors, there doesn't seem to be a need for an extra junction table.
    Attached Images Attached Images
    • File Type: png x.png (4.2 KB, 0 views)

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Junction Table for Junction Tables (2007)

    Sorry for not being clearer.

    The managers will be filing evaluations on the papers that will need to be distributed back to the authors.

    I will do this through reports (that aren't constructed yet).

    Would I be better off having a report use a query to the PaperManager table to get those evaluations, and then another query to the PaperAuthor table to figure out where to send them?

    If this is the case, where can I found out more about how to choose whether to put my data into more complex tables or queries?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Junction Table for Junction Tables (2007)

    As far as I can tell, it's only a shift in terminology - in the relationship chart I posted, replace Reports with Papers.

    The Papers table would be the central table. The PaperAuthors table tells you which authors are associated with a paper, and the PaperManagers table tells you which managers are associated with a paper. I don't see a need to create a junction table that relates authors to managers, or that combines papers, authors and managers.

    Microsoft has a general introduction to relational database design: "Understanding Relational Database Design" document is available in Download Center.
    A more in-depth treatment of this subject can be found in Steven Roman's book Access Database Design & Programming.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Junction Table for Junction Tables (2007)

    I think I'm good.

    Again, I wasn't as clear as I should have been: I should've originally called them papers rather than reports. When I used report on the initial post I didn't mean it in the Access sense, but on the last reply I meant report strictly in the Access sense.

    The Microsoft piece looks good and will help.

    Unfortunately, I already have the O'Reilly book, and apparently I'm not digesting it well enough.

    EOM

Posting Permissions

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