If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
many-to-many relationship
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! |
#2
|
|||
|
|||
many-to-many relationship
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. -- KARL DEWEY Build a little - Test a little "Christine" wrote: 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! |
#3
|
|||
|
|||
many-to-many relationship
On Thu, 2 Aug 2007 08:50:05 -0700, Christine
wrote: 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? 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. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...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] |
#4
|
|||
|
|||
many-to-many relationship
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. "John W. Vinson" wrote: On Thu, 2 Aug 2007 08:50:05 -0700, Christine wrote: 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? 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. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...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] |
#5
|
|||
|
|||
many-to-many relationship
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 "KARL DEWEY" wrote: 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. -- KARL DEWEY Build a little - Test a little "Christine" wrote: 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! |
#6
|
|||
|
|||
many-to-many relationship
On Thu, 2 Aug 2007 10:14:03 -0700, Christine
wrote: 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. 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] |
Thread Tools | |
Display Modes | |
|
|