 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / February 2007junction tables
Per access help, i have created a junction table between ContactsTbl and CompanyTabe to create a many to many relationship between the two tables. The junction table contains ContactID and CompanyID fields from each each of the tables. I've created a relationship between ContactID in ContactTbl and ContactID in Junction table as well as a relationship between CompanyID in CompanyTbl and CompanyID in junction table. I'm having trouble wrapping my brain around the needed relationships...and whether or not they should have referential integrity. i would think that when a company is deleted, i would want any corresponding contacts in the company deleted but if i deleted contacts, obviously wouldn't want the company deleted. Any suggestions on what types of joins? are the relationships i created correct? Thanks Lefty Enforce referential integrity on both relationships. Generally it's not a good idea to cascade deletes anywhere, but in this case, if a contract is deleted, you want the detail record that represents the compound key, to also delete, so turn on Cascade deletes from Contracts to the junction table.  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Per access help, i have created a junction table between ContactsTbl > and CompanyTabe to create a many to many relationship between the two [quoted text clipped - 11 lines] > Thanks > Lefty > Enforce referential integrity on both relationships. Generally it's not a > good idea to cascade deletes anywhere, but in this case, if a contract is [quoted text clipped - 21 lines] > > - Show quoted text - Thank you, I'm assuming that by contracts you mean contacts? Just so i can be sure. Yes, Contacts and ContactID SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >> Enforce referential integrity on both relationships. Generally it's not a >> good idea to cascade deletes anywhere, but in this case, if a contract is [quoted text clipped - 26 lines] > I'm assuming that by contracts you mean contacts? Just so i can be > sure. > Yes, Contacts and ContactID > -- [quoted text clipped - 36 lines] > > - Show quoted text - Thanks for the help. I set those relationships up but am haveing a problem with creating subdatasheet in the CompanyTbl. i selected the junction table in the subdatasheet wizard but don't know what to do with the child and master fields. it seems every combination i try reterns no records in the subdatasheet. any thoughts? thanks > Thanks for the help. I set those relationships up but am haveing a > problem with creating subdatasheet in the CompanyTbl. > i selected the junction table in the subdatasheet wizard but don't > know what to do with the child and master fields. it seems every > combination i try reterns no records in the subdatasheet. any > thoughts? Subdatasheets are only of any good in tables, and you should not ever be editing data in tables. Subdatasheets will slow down the connection of data immeasureably and should really never be used at all. Use a form and subform for your data entry/editing. With the Master/Child connection in a Form/Subform, you use the same field that you used when you set up the PrimaryKey/ForeignKey relationship. As an example, look at the Northwind sample database that came with Access. Look at the relationships between the Orders and OrderDetails tables and the relationship between the Products and OrderDetails tables. OrderDetails in the junction table. Now look at the Master/Child link in the form/subform for orders. That should help you understand. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > > Thanks for the help. I set those relationships up but am haveing a > > problem with creating subdatasheet in the CompanyTbl. [quoted text clipped - 17 lines] > -- > Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com Thanks for the tip. i'd rather use forms anyway for data entry because there will be less informed users of this database and i'd rather keep them away from tables. I will look closly at Northwind. > > Yes, Contacts and ContactID > > -- [quoted text clipped - 50 lines] > > - Show quoted text - After looking a little closer, i realize that there is not data in my junction table. i don't think i'm using the junction table and relationships correctly. i thought that the relationship would cause the junction table to populate with new records that correspond with records in the contact and company tables. in other words, with both table linked to the junction table, i thought it would create new records in the junction table for each company/contact combination. am i missing the boat on this one? thanks > After looking a little closer, i realize that there is not data in my > junction table. i don't think i'm using the junction table and [quoted text clipped - 4 lines] > records in the junction table for each company/contact combination. am > i missing the boat on this one? That boat was never built <g> You could build an Append query and force all the combinations, but that is like creating lots of dummy records. The thing that makes a relational database so efficient is that In a one-to-many or many-to-many relationship there does not have to be data. So in the OrderDetails table, every order does not have to contain every product. To add data to the junction table, go to the company record that you want, use a hidden textbox for the CompanyID in the subform and a combobox for the ContactID (so you can show the contact's name instead of ID). As you select contact names, you populate rows which creates the data you are looking for. The Compound Primary Key makes it impossible for you to select the same contact twice for that company. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com Is the relationship type between Companies and Contacts really many-to-many? That would mean that each contact can belong to more than one company, which is perfectly feasible of course, but unusual. Mostly a contact can belong to one company only, so the relationship type is one-to-many, merely requiring a CompanyID foreign key in the Contacts table. Incidentally you can model a one-to-many relationship type with a table, just as you'd model a many-to-many relationship type. The difference is that the column in the 'junction' table which references the table on the one-side (the referenced table), CompanyID in this case, is indexed uniquely. The reason for doing this is that it avoids any Null foreign keys in the table on the many-side (the referencing table) if it does not reference a row in the referenced table (a contact who is such as an individual, not belonging to a particular company in this case). A Null, being the absence of a value rather than a value as such is semantically ambiguous. Opinions vary on their use, some people are prepared to allow them, others not. Ken Sheridan Stafford, England > Per access help, i have created a junction table between ContactsTbl > and CompanyTabe to create a many to many relationship between the two [quoted text clipped - 11 lines] > Thanks > Lefty On Feb 25, 1:16 pm, Ken Sheridan <KenSheri...@discussions.microsoft.com> wrote: > Is the relationship type between Companies and Contacts really many-to-many? > That would mean that each contact can belong to more than one company, which [quoted text clipped - 33 lines] > > - Show quoted text - Thanks for the response. I would tend to agree with you regarding the first point but i have a couple of clients that will occaisionally switch companies and even sometimes will work for more that one company at the same time. clients that work for more than one company at the same time are rare but do occur. i'm pretty green with the application but thought this type of relationship would be the way to go in this case. In that case the relationship type is many-to-many, so your model is spot on. Ken Sheridan Stafford, England > Thanks for the response. I would tend to agree with you regarding the > first point but i have a couple of clients that will occaisionally [quoted text clipped - 3 lines] > application but thought this type of relationship would be the way to > go in this case. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.