Updating the 1 side of a 1:Many Relationship

[Guest]

I've got a table (tblHouseholds) consisting of address information with an
additional field that stipulates the PrimaryMember (head of the household
from a related tblMembers table).

My problem is that as new records are added to tblHouseholds, PrimaryMember
is Null. I'm trying to figure out how to extract the MemberID of the first
record that was subsequently entered into tblMembers and insert it into
PrimaryMemberID.

I tried creating a qryFirstMembers query to bring the "Many" table down to
"1":
SELECT DISTINCT tblMembers.HouseholdID, First(tblMembers.MemberID) AS
FirstMemberID
FROM tblMembers
GROUP BY tblMembers.HouseholdID;

I then tried an update query by relating tblHouseholds to qryFirstMembers:
UPDATE tblHouseholds LEFT JOIN qryFirstMembers ON tblHouseholds.HouseholdID
= qryFirstMembers.HouseholdID SET tblHouseholds.PrimaryMember =
[FirstMemberID]
WHERE (((tblHouseholds.PrimaryMember) Is Null));

When I attempt to run the Update query, I'm challenged with a "Operation
must use an updatable query. (Error 3073)"

I can manually update PrimaryMember by hand but need to be able to update
records through a query or some other automated process.

Any ideas?

dbqph

 

[Allen Browne]

This is a thorny issue: if every record in the household has a PrimaryMember
field, does your structure introduce the possiblity of being inconsistent?
Would it be wrong if one member has someone as the primary member, yet
another person in the same household has another person as the primary
member? If so, you might consider a different design.

Here's an alternative:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
Download the sample database, and see if it does what you need. tblGroup is
a household. tblGroupClient contains the members of the household, and each
member has a role. By assigning a priority to each role (in tblRole), you
could derive who is the primary member of the household.

 

[Guest]

Allen,

Thank you for the insight. I reviewed your application and hope an alternate
solution exists that wouldn't require me to re-design the structure of the
database.

I chose the design structure primarily for simplicity (figuring that if each
household has a primary member the value of that member should be stored in
the household table). The problem is that this value can't be determined
until:

1. a household has been created, then,
2. a member of the household has been created.

Your approach in the sample application appears that it could solve the
problem I'm facing. However, short of the Household/PrimaryMember
relationship, no other roles within the context of the Household/Member
relationship are foreseen.

Perhaps I should consider a third table (in the vein of your structure) that
contains HouseholdID and PrimaryMember with each field set to Entry Required
= Yes and Indexed, No Duplicates?

--
Ladd Nelson

This is a thorny issue: if every record in the household has a PrimaryMember
field, does your structure introduce the possiblity of being inconsistent?
Would it be wrong if one member has someone as the primary member, yet
another person in the same household has another person as the primary
member? If so, you might consider a different design.

Here's an alternative:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
Download the sample database, and see if it does what you need. tblGroup is
a household. tblGroupClient contains the members of the household, and each
member has a role. By assigning a priority to each role (in tblRole), you
could derive who is the primary member of the household.

<snip>

 

[Guest]

I was able to arrive at my desired results by making a temporary table and
then running an update query using the temporary table rather than trying to
do it all on-the-fly:

qryMakeTemp:
SELECT tblMembers.HouseholdID, First(tblMembers.MemberID) AS FirstOfMemberID
INTO tblOKToDelete
FROM tblMembers
GROUP BY tblMembers.HouseholdID;

qryUseTemp:
UPDATE tblHouseholds LEFT JOIN tblOKToDelete ON tblHouseholds.HouseholdID =
tblOKToDelete.HouseholdID SET tblHouseholds.PrimaryMember =
tblOKToDelete.FirstOfMemberID
WHERE (((tblHouseholds.PrimaryMember) Is Null Or
(tblHouseholds.PrimaryMember)=0));

I then used a bit of code to run the routines as needed by the user:

Public Function GetSetPrimaryMembers()

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeTemp"
DoCmd.OpenQuery "qryUseTemp"
DoCmd.DeleteObject acTable, "tblOKToDelete"
DoCmd.SetWarnings True

End Function

I'm sure there are more sophisticated approaches to the problem, but this
seems to be the most painless for what I'm trying to accomplish.

dbqph