Results 1 to 3 of 3
  1. #1
    Join Date
    May 2015

    Unanswered: Wood from the trees


    I want to build a database with Access to manage volunteers who will be helping out at a number of events. The data-entry will be contact-centric, with contacts/volunteers assigned to one or more events.

    I've previously done similar using a one-to-many: the main volunteer/contact form contains an event-subform. It's simple enough then to query all contacts assigned to any particular event.

    What I really want is to understand how using a many-to-many relationship (which I guess could equally apply) would add power or flexibility to what I could do or how I could query the tables. I have the feeling it must offer advantages - but I'm not quite sure what these are.

    Many thanks for your help with this one.

    Cheers - Jol

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    its very tricky in the db world to create a many to many relationship. however you could get in touch with your feminine side and fake it by using an intersection table
    essentially you create a table for events
    a table for people
    and an intersection table which identifies what people are allocated to waht event (and any other data relevant to that intersection.

    quite often the intersection tabel stores ONLY the fact that its priamry key is made up tof a composite of other tables primary keys. you can have more than two 'parent' tables as part of the intersection many to many realtionship.

    I think you will benefit from looking in more detail at normalisation and trying to assign data to data stores which only have data relevant to that section.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Provided Answers: 32
    How about this example of 1-many tables:

    1 bowling alley has many Tournaments
    1 tournament has many leagues
    1 league has many teams
    1 team has many players (4 actually)
    1 player has many phones

    Every year is a new tournament (usu by year)
    So you want to find a person amoung many years of play...
    The query would link, player to team to league to tourney.

    This way you dont have to keep adding fields, just data.

Tags for this Thread

Posting Permissions

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