Relating subform data

[megbowlstrike]

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?

 

[John W. Vinson]

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?

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]

 

[megbowlstrike]

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]

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.

 

[megbowlstrike]

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.- Hide quoted text -

- 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.

 

[John W. Vinson]

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)

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
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."

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]

 

[megbowlstrike]

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.



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!!


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]- Hide quoted text -

- 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!

 

[John W. Vinson]

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]