many-to-many relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have what should be a simple database. I have only three tables:

1 - who - names, category (client etc...)
2 - where - contact info (most people will have more than 1 set of contact
data. Some will have 3)
3 - what - event information (most people will have more than 1 set of data
in this table also).

It would be easy if table 2 had one set of data. Then it would be a
one-to-one, then 2 & 3 would be a one-many. I've done that before. Now
it's a one-to-many between 1&2 and a many-to-many between 2&3. I am having
problems with the many to many relationship. I have tried creating a junction
table but get stuck on the primary keys. How can you have a primary key when
you need duplicate identifiers?

I really need a simple, clear, idiot proof source for this information. (I
already tried the help section on the MS website). Any ideas would be
gratefully welcom.

Thank you!
 
Maybe I am not understanting the data you are recording but it seems like you
would have one-to-many of Who to Events and have one-to-many of Events to
Where.
John Smith
Birthday Party
Office
Home
Church
Lodge
Traffic Ticket
Parking Lot
Main Street
Highway 66
If I am all wrong in my thinking then post back data sample for your
situation.
 
I have what should be a simple database. I have only three tables:

1 - who - names, category (client etc...)
2 - where - contact info (most people will have more than 1 set of contact
data. Some will have 3)
3 - what - event information (most people will have more than 1 set of data
in this table also).

It would be easy if table 2 had one set of data. Then it would be a
one-to-one, then 2 & 3 would be a one-many. I've done that before. Now
it's a one-to-many between 1&2 and a many-to-many between 2&3. I am having
problems with the many to many relationship. I have tried creating a junction
table but get stuck on the primary keys. How can you have a primary key when
you need duplicate identifiers?
Click to expand...

The missing piece is that a Primary Key can consist of TWO fields - it needn't
be just one!

If the primary key of 2 is ContactID, and that of 3 is EventID, you can create
a new table (Participation say) with fields for ContactID and EventID.
Ctrl-click both of these fields in table design view and select the Key icon.

This will allow duplicate values in ContactID (one event may involve many
contacts) or in EventID (one contact may be involved in many events) but the
combination of the two must be unique - you can't enter the same person twice
for the same event.
I really need a simple, clear, idiot proof source for this information. (I
already tried the help section on the MS website). Any ideas would be
gratefully welcom.
Click to expand...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Check Crystal's tutorial and the Database Design 101 links on Jeff's site
particularly.

John W. Vinson [MVP]
 
The lightbulb starts to flicker!!

Would the new table contain just the primary keys? If so, would they be
numbers and indexed? I had assumed that the PKs would be indexed Yes (Dups.
OK). This appears to be where I'm getting stuck.
 
I read John's post first (and replied) then yours. You're correct - the Who
table has one-to-many relationship with each of the other two tables. What
I am trying to do is relate the the Where and Event tables.

What you're sample data doesn't do is connect the Birthday Party to the
Traffic Ticket (or in my case a conference to mailing info).

John Smith
Conference 1A: city, facility, cost, attending etc...
Contact Info: for mailing invitation

I'll mainly need to do this in queries and for data entry as the contact
info changes (frequently)

thank you
 
The lightbulb starts to flicker!!

Would the new table contain just the primary keys? If so, would they be
numbers and indexed? I had assumed that the PKs would be indexed Yes (Dups.
OK). This appears to be where I'm getting stuck.
Click to expand...

Each field would be indexed, dups allowed. Actually you needn't (shouldn't!)
do this in the field properties or the Indexes tool - instead, create
Relationships from the Contact and Events tables to these numeric foreign key
fields. Access will create the necessary indexes for you in the process. Once
you're done, there will be nonunique indexes on each of the fields, *and* a
two-field Primary Key index spanning both fields.

This table might or might not have additional fields. I don't know what kind
of events these are, but I can imagine a "Role" field in which you can
indicate that person A is the Chairman for event X, and persons B and C are
on the Cleanup Crew.

John W. Vinson [MVP]
 
Back
Top