Many-to-many implementation problem

A

Al Williams

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

Allen Browne

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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.
 
A

Al Williams

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.

Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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.
 
A

Allen Browne

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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.

Allen Browne said:
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


Al Williams said:
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.
 
A

Al Williams

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


Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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.

Allen Browne said:
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


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

Al Williams

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

Al Williams said:
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


Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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

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


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

Allen Browne

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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


Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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

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


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

Al Williams

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


Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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


Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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


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

Allen Browne

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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


Allen Browne said:
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.

Al Williams said:
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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


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

Al Williams

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

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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


Allen Browne said:
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.

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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


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

Allen Browne

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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]
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.

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
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

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


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

Al Williams

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


Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
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

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


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

Al Williams

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

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
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

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


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

Allen Browne

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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


Allen Browne said:
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.


Al Williams said:
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:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

message
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
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

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


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

Al Williams

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



Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al Williams said:
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


Allen Browne said:
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.


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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

message
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
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

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


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

Allen Browne

Well done!!! That's a great experience.

And when you finally nail it, very rewarding.

All the best for the next one too.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top