One-to-One Relationships and Subforms

[Glen Richards]

Hi,

Background to a problem:

I have 3 tables: Person (Welfare Client), Volunteer, & Member.

The Person table has base data like name, address & phone numbers. The
Volunteer table has a one-to-one relationship to Person and includes
some extra fields for volunteers. The Member table has a one-to-one
relationship to Volunteer and it includes extra fields for members. A
member is always a volunteer. A volunteer is always a person - but a
person may be just a welfare client (& not a volunteer).

I've created a main form for person which has a subform for the
volunteer. The volunteer subform has a subform for member.

Scenario 1: A foreign key for person is in the volunteer table & a
foreign key for volunteer is in the member table. With this, the
subforms linked properly, but I couldn't enter new member information
(I'm guessing because a member doesn't have a direct link to person).

Scenario 2: A foreign key for volunteer is in the person table & a
foreign key of member is in the volunteer table. With this, I can
enter new member information but the subforms don't link correctly (I
get "You tried to assign the Null value to a variable that is not a
Variant data type"). (perhaps I could jump into code at this point &
assign the Id to the correct field - as Access can't do this
automatically in this situation)

Also with scenario 2, I tried reversing my subforms (person is a sub of
volunteer, which is a sub of member... but the rule that a member must
be a volunteer was not being enforced i.e. I could create a member
without relating a volunteer to it.

Why am I doing this? I'm trying to be economical in my field usage by
not repeating name, address etc in each table to make future changes
easier. I'm also trying to cut back on form creation i.e. using 1 form
for each type table & just hide the appropriate subforms - again to
make maintenance easier.

So, can anyone (that's still with me), see a solution to my problem?
Or should I give up & go to a simpler design?
Any help much appeciated,


Glen Richards

 

[Allen Browne]

How many fields do you have that apply to Member and not Volunteer, or
Volunteer and not member? In my experience, you can generally just stick all
the fields in the one table, and then create separate forms for entering
Members and Volunteers. To the user it looks like 2 different concepts
because that's how the user conceives of the data, but in reality you have
only one thing to manage. Unless your field count is getting past 70, that's
probably the way to go.

 

[Glen Richards]

Person has about 12, Volunteer 4, Member 32
Your advice sounds like the way to go.

Cheers,


Glen