 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / November 2007Relating subform data
Hopefully I'll be able to make this easy to understand. I have several tables in my db, but the ones I'm concerned with at the moment are tbl_Plan and tbl_Distribution. tbl_Plan contains information about an employee's retirement plan. tbl_Distribution contains payout/ distribution information for the plan in question. I have a subform for tbl_Plan and a subform within that for tbl_Distribution. When I move through the records in the Plan subform, it shows the correct records in the Distribution subform. Here's the problem. For employee #1, there are two plans. The first plan has one distribution and the second plan has a different distribution. However, both distribution records are displayed, no matter what plan record is selected. So when you scroll through the different plans, you aren't sure which plans the employee's distributions are related to because all distributions are displayed, even if they aren't related to the plan record that is being viewed. How can I relate the distribution data to the employee's individual plans? >I have a subform for tbl_Plan and a subform within that for >tbl_Distribution. When I move through the records in the Plan [quoted text clipped - 8 lines] >How can I relate the distribution data to the employee's individual >plans? How are the *tables* related? Get their relationship right first... the forms will follow. Also, what are the Master and Child Link Fields of the subform control? John W. Vinson [MVP] On Nov 26, 7:18 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > >I have a subform for tbl_Plan and a subform within that for > >tbl_Distribution. When I move through the records in the Plan [quoted text clipped - 15 lines] > > John W. Vinson [MVP] I'm actually having trouble relating the two tables. Here is my field list: tbl_Plan: Plan ID (primary key; autonumber) Employee ID (foreign key that links to tbl_Employee) Plan Name Participant From Participant To Initial Plan Entry Comments tbl_Distribution: Distribution ID (primary key; autonumber) Employee ID (foreign key that links to tbl_Employee) Distribution Taken Payment Type Amount Currency Date Balance Comments Plan ID (I added this yesterday in hopes of adding a relationship between the two tables; number field) I set the relationship as one-to-many with tbl_Plan as the one and tbl_Distribution as the many. When I try to enter a record it gives me an error that says "You cannot add or change a record because a related record is required in table tbl_Plan." I didn't see the master and child link fields on the subform's properties. Really I think that if I can get the relationship working then everything else will fall into place. On Nov 27, 10:04 am, megbowlstr...@sbcglobal.net wrote: > On Nov 26, 7:18 pm, John W. Vinson > [quoted text clipped - 54 lines] > > - Show quoted text - I got the error to quit appearing by erasing the default value of 0 but the relationship still doesn't appear to be working. Thanks for your response--further help would be appreciated. :) >I got the error to quit appearing by erasing the default value of 0 >but the relationship still doesn't appear to be working. Thanks for >your response--further help would be appreciated. :) Again: you need to - somehow, I don't know how because I don't know your data - get the PlanID inserted into the existing records in your distributions table. What you have now is the employeeID in both the Plans and Distributions table. I'm assuming that it should NOT exist in the distributions table; you only need the PlanID. What you might be able to do - back up your database first!! - is update the (now empty) PlanID field to the PlanID field from Plans, linking by employeeID. This will fail if an employee has multiple plans... but without knowing which distribution corresponds to which plan, there's no way Access (or any program!!) can update the PlanID appropriately. You may have to go back to paper to figure out which distribution goes with which plan. John W. Vinson [MVP] >I'm actually having trouble relating the two tables. Here is my field >list: [quoted text clipped - 11 lines] >Distribution ID (primary key; autonumber) >Employee ID (foreign key that links to tbl_Employee) That's your problem. A distribution is only *indirectly* related to an employee! It's a distribution *from a plan*, and you should have Employees related one to many to plans (as you do, above); and Plans related one to many to Distributions. One question though - should there not be a many to many relationship from employees to plans? Surely each Plan involves many employees, right? Wouldn't there need to be a table of plan names, terms, description, etc...? As it is you'll be repeating the plan name in tbl_Plan for every employee in that plan. >Distribution Taken >Payment Type [quoted text clipped - 10 lines] >me an error that says "You cannot add or change a record because a >related record is required in table tbl_Plan." That's because the new PlanID field (don't use blanks in fieldnames!!!) is either NULL or defaulting to zero. You can't just put in the field and expect Access to automagically figure out which PlanID ought to be put into it!! >I didn't see the master and child link fields on the subform's >properties. Really I think that if I can get the relationship working >then everything else will fall into place. These are properties *of the subform control*, the empty box containing the form - not properties of the form object within that control. Select the edge of the subform, or use the dropdown list of controls on the left end of the toolbar. John W. Vinson [MVP] On Nov 27, 11:31 am, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > >I'm actually having trouble relating the two tables. Here is my field > >list: [quoted text clipped - 53 lines] > > - Show quoted text - Thanks so much for your help John! I managed to get the subform working by deleting the relationship between tbl_Plan and tbl_Employee. Then I changed the master/child links to PlanID (thanks for your tip on the field names). I haven't used Access in a long time but studied it in school, so it's starting to come back to me a little bit. I didn't do a separate table for Plan names because it is likely that they will all be different--there are no set names. The database houses international employees and it seems that every employee is in a differently-named plan. I was going to have a set list of plan names for the user to choose one from a drop down list but after talking to the user and looking at the data, it seems like a better idea to let him/her type in the plan name. Thanks again for your help! :) |
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.