 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / April 2005Many-to-many implementation problem
Access 2003: I have a database containing contact information and other attributes for members of an organization. Some of those members work for other organizations. In some cases, they work for the same other organization. In those other organizations, they may have more than one boss. In some cases they have the same boss(es) in the other organization and in other cases they have unique boss(es). The intent of the database design is to view/enter the contact information for each member and, if they work for another organization, to view/enter that organizations’s address, etc and to select the member’s boss(es) in that organization. I’m getting an error message from Access when I try to select the member’s boss(es). Because of the relationships between the tables, I’m guessing that the problem is in my table design and not in the queries, form/subform links or row sources. The error message is: “ You cannot add or change a record because a related record is required in table ‘OtherOrg’ ” My table design is: tblBaseOrgMembers MemberID (autonumber) (PK) LastName FirstName City Etc tblOtherOrg OtherOrgID (autonumber) (PK) OtherOrgName City Etc tblOtherOrgLeaders OtherLdrID (autonumber) (PK) OtherOrgID (FK) LastName FirstName City Etc tblBaseOrg-Other [junction table with all three fields part of the composite foreign key, which is indexed] MemberID (long integer, not required, not indexed) OtherOrgID (“, “, “) OtherLdrID (“, “, “) The junction table relationships are: tbleBaseOrgMembers:MemberID -> MemberID (1:M) tblOtherOrg:OtherOrgID -> :OtherOrgID (1:M) tblOtherOrgLeaders:OtherLdrID -> OtherLdrID (1:M) with referential integrity enforced. Also, there is a relationship between tlbOtherOrg and tblOtherOrgLeaders which uses OtherOrgID (1:M) Data entry: I’ve successfully loaded the OtherOrg and OtherOrgLeaders tables with data using a form/subform designed just for that purpose. I have another form for BaseOrg data entry which also contains a combo box that selects the desired OtherOrg. That combo box is used to filter the universe of OtherLdr to just those possible leaders for that BaseOrg member. When I attempt to select one of the possible leaders on a subform (part of the BaseOrg form), I get the error message from Acess: “ You cannot add or change a record because a related record is required in table ‘OtherOrg’ ” Does anyone see a problem with this table design? Thanks.  SignatureAl Williams Hi Al Didn't actually read through all this carefully, but you can get that error message if your subform is based on a query, and one of the fields from the other table contains a Default Value. Access misinterprets the default value and complains that it can't add the record to the lookup table. For another possible way to model your scenario, see: People in households and companies at: http://allenbrowne.com/AppHuman.html SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Access 2003: > I have a database containing contact information and other attributes for [quoted text clipped - 76 lines] > > Thanks. Allen, 1. Yes, the subform that I'm having problems with does use a query that includes all three fields from the junction table's composite foreign key and all the fields from tblOtherOrgLeaders. I'm very new at many-to-many relationships and followed the pattern of a successful many-to-many implementation that had two M:M tables - but not three like I'm doing. Since it may be that the composite foreign key in the junction table isn't being updated properly, let me describe how I'm trying to do the update. I believe that the first field in the junction table is defined at time of update by the form/subform Master Field/Child Field linkage between the BaseOrg and the OtherOrgLeaders subform. The third field is defined by the combo box selection in the OtherOrgLeaders subform. It's the second field, OtherOrgID that I'm not certain is being handled correctly. I'm assuming that since the OtherOrg combo box on the BaseOrg form does cause the proper subset of OtherOrgLeaders to be available, that that information is available to the query for the row source. In that query, I'm referencing the BaseOrg combo box by using a Forms![..]![..] expression. So, to me everything seems to be there and that everything is in place for a simulataneous update of all three fields in the CFK - it just doesn't work. :( Do you see a problem? 2. Your People in Households and Companies article is very interesting. I'm going to have to read it carefully. Thanks. > Hi Al > [quoted text clipped - 88 lines] > > > > Thanks. Hi Al I think you have: - a main form bound to [tblBaseOrdMembers], and - a subform bound to a query based on [tblBaseOrg-Other]. The LinkMasterFields/LinkChild fields will therefore be [MemberID]. In the subform, you have 2 combos: - one to choose the [OtherOrgId]; - one to choose the [OtherLdrId]. The subform will inherit the [MemberId] from the main form. To get this working, remove the other tables from the query, so it only has the 3 fields from [tblBaseOrg-Other]. Once you have that working, you can go back and add tblOtherOrg to the query, presumably to get OtherOrgName into the query output grid. In query design view, double-click the line joining the 2 tables. Access pops up a dialog giving 3 options. Choose: All records from tblBaseOrg-Other, and any matches from ... Test the subform. If it fails at this point, open tblOtherOrg in design view, and remove the Default Value for any fields. Also check that the text boxes in the form for the fields from tblOtherOrg have not Default Value set. (You also want to set the Locked property for these text boxes to Yes, so the user cannot accidentally change them: these fields will be for information only.) Once you have that working, you can then open the query in design view, and add tblOtherOrg to the query. Make it an outer join also, and make sure that there is no Default Value for any fields in this table, nor for the text boxes bound to this table. You should now have it working as you wanted. On a broader view, your structure is perhaps not ideal. In tblOtherOrgLeaders, any leader can belong to only one "other org". Therefore, choosing a value in the [OtherLdrId] combo means you already know which [OtherOrgId] that leader belongs to. [tblBaseOrg-Other] therefore should not have a field for [OtherOrgId]. In fact, having both fields in [tblBaseOrg-Other] leaves the door open for bad data. Beyond merely dropping the [OtherOrgId] field from [tblBaseOrg-Other], you might consider a structure where there are just two tables: - one for tblOrg (OrgId, OrgName, ...) - one for tblPerson (PersonId, LastName, FirstName, ...) Put your own organisation in tblOrganisation. Put your own staff and everyone elses in tblPerson. Now add a 3rd table that says who works for whom, under which boss: tblOrgPerson: OrgPersonId AutoNum p.k. PersonID Number f.k. to tblPerson.PersonId. Required. OrgId Number f.k. to tblOrg.OrgId. Required. RoleId f.k. to tblRole.RoleId. Optional BossId Number f.k. to tblPerson.PersonId. Optional This 3rd table lets you enter who works for which organisation, in what role (secretary, manager, janitor, ...) and who they report to in that role at that organisation. This simple structure lets a person work part-time under different bosses (or the same boss) in different organisations (or the same organisation), and it's incredibly simple to maintain. Perhaps that would not achieve what you want, but it certainly seems like the most flexible approach from what we know of your case. HTH SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 144 lines] >> > >> > Thanks. Hi Allen, Thank you very much for responding. I know that working your way through my info took time and I appreciate it. It has taken me some time to work through your suggestions because I've taken a make one change and evaluate the results approach. I haven't gotten it working yet but I think I can give a better description of what the problem is. I also realize that I didn't I describe my table design sufficiently. There are three tables. The BaseOrg has all the members of the organization I'm doing the database for. Some of those members work for another organization (only one organization) which is described in [tblOtherOrg] but they may have none, one, or many bosses [tblOtherLdr]. The junction table [tblBaseOrg-Other] has a CFK that contains only foreign keys corresponding to the three primary keys from the other three tables. The form for [tblBaseOrg] also has a combo box based on a query the selects [tblOtherOrg].[OtherOrgID] and [tblOtherOrg].[OtherOrgName]. The result is bound to a field that I put into [tblBaseOrg] for that purpose. The query for the subform that selects [tblOtherOrgLeaders] entries is based on [tblBaseOrg-Other] and [tblOtherOrgLeaders]. That subform has a combo box for selection of the none (which is a N/A entry which is already in [tblOtherOrgLeaders]), one, or many bosses. That combo box is based on a query using only [tblOtherOrgLeaders] with [tblOtherOrgLeaders].[OtherOrgID] having its criteria set to [Forms]![BaseOrg]![OtherOrgID]. Both fields are a foreign key for [tblOtherOrg].[OtherOrgID]. Here is what I've done: 1. As suggested, I deleted the field for [OtherOrgId]. You are correct, I know in advance that there can be only one OtherOrg for a member. 2. I changed all my queries for the combo boxes and and subform to saved queries (I read in one of the newsgroup postings that that sometimes helps with update problems). 3. I've tried multiple iterations (all I can think of) including and excluding fields in the queries - to no avail. 4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and [tblOtherOrgLeaders] to see what would happen. As a result, the combo box in the subform does update BUT instead of using existing [tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to populate [tblBaseOrg-Other] the combo box selection is adding entries to [tblOtherOrgLeaders]. That's why Access is complaining; there is no simultaneous updates to create new records in [tblOtherOrg]. To try to resolve the problem, I removed all references to any [tblOtherOrg] fields in the queries and also removed any references to the foreign key, [tblOtherOrgLeaders].[OtherLdrID]. That didn't resolve it. I'm now stuck because I don't have any more ideas. I did read your People in Households and Companies article and think it is a very good. My database is already deployed with seven people using it. So, if at all possible, I need to try to figure out a way to live with my existing architecture. Do you have suggestions? Thanks. Al > Hi Al > [quoted text clipped - 211 lines] > >> > > >> > Thanks. Allen, I should have said that the members may work for only one organization at a time, but there are several organizations that they might for. Hence, the 1:M between [tblOtherOrg] and [tblOtherOrgLeaders]. Al > Hi Allen, > [quoted text clipped - 272 lines] > > >> > > > >> > Thanks. The core of the issue seems to be under your #4: 4. Finally, I deleted the 1:M relationship between [tblOtherOrg] and [tblOtherOrgLeaders] to see what would happen. As a result, the combo box in the subform does update BUT instead of using existing [tblOtherOrgLeaders].[OtherOrgLdrID] entries in [tblOtherOrgLeaders] to populate [tblBaseOrg-Other] the combo box selection is adding entries to [tblOtherOrgLeaders]. That's why Access is complaining; there is no simultaneous updates to create new records in [tblOtherOrg]. You are expecting Access to add new records to or perform updates on tblOtherOrg as well? It is not going to do that. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi Allen, > [quoted text clipped - 310 lines] >> >> > >> >> > Thanks. Hi Allen, No, I don't want Access to add new records to or perform updates on tblOtherOrg. To the contrary, I wanted to take information from tblOtherOrgLeaders and use that to update the junction table, tblBaseOrg-Other, while also updating the junction table with information from tblBaseOrg. That's what puzzling me. I don't understand why Access thinks I'm trying to update tblOtherOrg. Al > The core of the issue seems to be under your #4: > 4. Finally, I deleted the 1:M relationship between [tblOtherOrg] [quoted text clipped - 323 lines] > >> >> > > >> >> > Thanks. If Access thinks you are trying to update tblOtherOrg, the only things I can think of are: a) Make sure the field from the correct table is in the query, and b) Make sure none of the fields in tblOtherOrg have a Default value set. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi Allen, > [quoted text clipped - 381 lines] >> >> >> > >> >> >> > Thanks. Allen, a. Except for one, each query is based on only one table. They are: qrycboOtherOrg which is used for the combo box on the BaseOrg form. That query is based on based on tblOtherOrg and returns fields OtherOrgID and OtherOrgName. It returns the proper information. The exception, the query used for the subform, is qryBaseOrgtoOtherOrgLdrs which contains both FK fields from the junction table, tblBaseOrg-Others, and attribute fields from tblOtherOrgLeaders. It does not contain the PK for tblOtherOrgLeaders nor the FK, OtherOrgID. The query used on the subform for selection of OtherOrg Leaders is qryRowSrceOtherOrgLeaders which is based on tblOtherOrgLeaders and contains the fields: LeadershipID, LastName, FirstName, and OtherOrgID. It does use the FK, OtherOrgID, with the criteria set to [Forms]![BaseOrg]![OtherOrgID] to filter the universe of OtherOrgLeaders down to matches for OtherOrg as selected by the combo box on the BaseOrg form. When the combo box on the subform is clicked, the proper subset of the universe of OtherLeaders displays. There may be a problem in there but I don't see it. b. Both tblOtherOrg and tblOtherOrgLeaders had a Date/Time field labeled DateUpdated with a default value of =Date(). I deleted the default value but continued to get the error message that a related record for tblOtherOrg was required. I also noticed that in tblOtherOrgLeaders, I had set the default value for the FK OtherOrgID to 0. When I deleted it, I got an error message that a null value was not allowed. That must have been why I put the default value in. When I changed the required property for OtherOrgID from Yes to No, I could select the proper subset of Leaders from OtherOrgLeaders but tblOtherOrgLeaders had additional new records recording my selections which means that my problem is still there. Al > If Access thinks you are trying to update tblOtherOrg, the only things I can > think of are: [quoted text clipped - 386 lines] > >> >> >> > > >> >> >> > Thanks. > I also noticed that in tblOtherOrgLeaders, I had set the default value for > the FK OtherOrgID to 0. Access does this with all numeric fields. It's a real pain, because 0 is usually a completely inappropriate value for a f.k. > When I deleted it, I got an error message that a > null value was not allowed. Ah, so this is the one that is not being populated, and the zero is the bad value. You need to somehow ensure that a valid value is being assigned to this field. It it is nominated in the LinkChildFields, then it should inherit value from the matching LinkMasterFields. If not, it needs to be assigned. > When I changed the required property for OtherOrgID from Yes to No No, you probably don't want to do that. A required f.k. is usually desirable. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 475 lines] >> >> >> >> > >> >> >> >> > Thanks. Allen, 1. I looked at the existing LinkMasterField and LinkChildField as you suggested. The Subform Field Linker window shows the Master and Child fields as MemberID with a message beneath saying: “Show qryBaseOrgtoOtherOrgLeaders for each record in <SQL Statement> using MemberID” The BaseOrg form is based on a SQL Statement, not a saved query which I think is the reason for the <SQL Statement> in the message. I believe that the above is correct because the entries are similar to those on the working form/subform for tblOtherOrg/tblOtherOrgLeaders 2. I modified the combo box on the subform for tblOtherOrgLeaders to show not only LastName and FirstName but to also show the value of the FK, OtherOrgID. I made the OtherOrgID control a combo box based on a query using the fields [tblOtherOrg].[OtherOrgID] (FK) and [tblOtherOrgLeaders].[OtherOrgID] (PK) When I opened the BaseOrg/OtherOrgLeaders form/subform. I observed that when I selected a leader, the FK, OtherOrgID, was forced to 0 and the write operation (the pencil symbol) did not complete. Using the combo box for the FK OtherOrgID I could select the valid FK and the write operation completed. Still, the operation added a new record to the OtherOrgLeaders table instead of only populating the junction table with entries. 3. I opened [tblOtherOrg]’s properties and observed that the subdatasheet property was set to [auto]. I changed that to Table.tblOtherOrgLeader and the LinkMasterField and LinkChildField properties automatically changed to OtherOrgID for the two entries. Still, when I tried to use the BaseOrg/OtherOrgLeaders form/subform, the FK OtherOrgID was forced to 0, again, and the write operation did not complete. Etc. Does the above provide a clue? Al > > I also noticed that in tblOtherOrgLeaders, I had set the default value for > > the FK OtherOrgID to 0. [quoted text clipped - 495 lines] > >> >> >> >> > > >> >> >> >> > Thanks. Hi Al. I probably can't spend more time on this q. Hope you can understand. It looks like you have figured out that the info is being written to the wrong table, and the circumstances under which this occurs. It is now a matter of figuring out why Access is writting back to that table. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen, > [quoted text clipped - 624 lines] >> >> >> >> >> > >> >> >> >> >> > Thanks. Hi Allen, I found it!!! I looked at the example implementation that I have more closely and discovered that in the subform for [tblOtherOrgLdrs] I needed to include both fields from the junction table – not just the OtherOrgLdrID field. That is, I needed to include MemberID. That took care of the writing records into tblOtherOrgLdrs. I also discovered that the same fields are required for the OtherOrgLdrID lookup query as in the query for the subform. That took care of a complaint by Access that fields were missing. It works. Whew!! Thank you very much, Allen. This is my first database and my first many-to-many implementation. There are so many variables to set and choose from in Access. If you hadn’t confirmed that I was basically on the right track and helped walk me through, I would still be stymied by uncertainty. Also, thank you for pointing out your People in Households and Companies article and sample database. I’ve been asked to do another database, for a homeschoolers school, and I’m planning on using your approach. I’m a retired electrical engineer and am doing these databases for free. It’s a great challenge. Thanks again, Al > Hi Al. > [quoted text clipped - 632 lines] > >> >> >> >> >> > > >> >> >> >> >> > Thanks. Well done!!! That's a great experience. And when you finally nail it, very rewarding. All the best for the next one too. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi Allen, > [quoted text clipped - 30 lines] > > Al Allen, I should add that I restored the Requirement of Yes to the FK OtherOrgID in tblOtherOrgLeaders and restored the defaults of =Date() for the Date/Time fields in tlbOtherOrg and OtherOrgLeaders. Al > > I also noticed that in tblOtherOrgLeaders, I had set the default value for > > the FK OtherOrgID to 0. [quoted text clipped - 495 lines] > >> >> >> >> > > >> >> >> >> > Thanks. |
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.