Results 1 to 2 of 2
2001-08-10, 18:42 #1
- Join Date
- Jun 2001
- New York, New York, USA
- 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.
2001-08-10, 20:34 #2
- Join Date
- Jun 2001
- Crystal Beach, FL, Florida, USA
- Thanked 37 Times in 36 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.