Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Basic Table Structure ? (ACCESS 97)

    I am redesigning a database from the ground up because it was designed hastily (by me) and has severe limitations. As I've been making new tables it occurred to me that I did not know when to use a "junction" table between 2 tables and when to use a foreign key only and bypass a junction table. I will have many forms that represent a portion of an entity (for each event that I track, there will be multiple attendees and approvals) attendees and approvals will be tables and a button will open forms based upon them. I don't know how to tell ACCESS that "when I click this button on this particular form, I want you to open up the attendee form for data entry with all the resulting atteendee names that I enter to be applied to this event) This is where the confusion for me comes in because should there be a junction table between the event table and the attendees table with an attendeeID and an EventID field only, or should the event table have a field that is attendeeID and that looks up matching values in the attendee table (or give me a blank record if there are not yet matching records).

    If anyone has a clue what I mean, then please set me straight if you can. Thanks for any help you can give. Jenn.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Basic Table Structure ? (ACCESS 97)

    You have an Event table and an Attendees table. The relationship between them is many-to-many. This is very difficult to work with. We want one-to-many relationships. That is, for each event we want to identify the many attendees; and for each attendee, we want to identify the many events he/she might attend.

    This is where that "junction" table is needed; which I've also seen called a "resolver" table. At a minimum, it has 2 fields: EventID and AttendeeID. These 2 fields make up the primary key for the table. Any other fields in this table may provide additional information about a specific Event/Attendee combination: Signup Date, Amount Paid, etc.

    A form based on the Event table can have a subform showing which attendees are coming to each event. An an Attendees mainform can have an Event subform showing all the events the Attendee is signed up for.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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