Records keep locking

[Ron]

I've got three related tables:
Members, Households, Addresses

Member to Household is many to one (household ID linking)
Household to Address is one to many

I want my query to list:
member name, household ID, and Address in a table format.

I've tried creating a query with all three tables, but
when Members and Addresses get together I get record
locking.

I can put Members & Households or Households and
Addresses, but Addresses and Members lock records when
together. I've even tried directly linking the
Member "household ID" to the Address "houshold ID", but
still record locking kicks in and I can't make changes to
the data.

Suggestions?

 

[John Vinson]

I've got three related tables:
Members, Households, Addresses

Member to Household is many to one (household ID linking)
Household to Address is one to many

I want my query to list:
member name, household ID, and Address in a table format.

I've tried creating a query with all three tables, but
when Members and Addresses get together I get record
locking.

Yes. The reason is that if a Household has three members, and two
addresses, there is no unique mapping from members to addresses.
You'll get all six possible combinations as grouped records. Therefore
there is no way for Access to update the tables - it cannot determine
which of the six combination records you intend to update!

To update these tables, the simplest approach might be to create a
Form based on Households with two separate subforms, based on Members
and Addresses respectively.

 

[Ron]

I was really trying to avoid using a subform for this.
I'm dealing with spreadsheet-mentality users and in this
particular case, the data would be easier to handle in
tabular view where they can just scan a list of
names/addresses.

Even though a Household can have multiple addresses,
there is only one "active" address per household.

I understand it is getting confused between the many-to-1-
to-many, but there must be some way to accomplish this.
Members/Households/Addresses are a fairly common
combination, and surely someone has overcome this before.


Ron.

 

[John Vinson]

I was really trying to avoid using a subform for this.
I'm dealing with spreadsheet-mentality users and in this
particular case, the data would be easier to handle in
tabular view where they can just scan a list of
names/addresses.

Even though a Household can have multiple addresses,
there is only one "active" address per household.

I understand it is getting confused between the many-to-1-
to-many, but there must be some way to accomplish this.
Members/Households/Addresses are a fairly common
combination, and surely someone has overcome this before.

If you can create and store a query selecting just the active address,
you should be able to join that Query to Households and then to the
people table. This will be updateable. Of course the household and
address information will be repeated as many times as there are
people!

 

[Ron]

OK, I've created an "active address" query (Qry1)and
verified that it only returns one address per household.
Qry2 is Member and Household tables plus Qry1. All three
are linked by household_ID. No dice, still record
locking.

Second attempt:
Using Qry1 from above. Qry2 just brings in Members and
Households. Qry3 uses Qry1 & Qry2 and links on
Household_ID. No dice, still record locking.

Third attempt:
Qry1 active addresses. Qry2 Households, Qry3 Members,
Qry4 uses 1-3. No dice.

Forth attempt:
Qry1 active addresses. Qry2 is Household table and Qry1.
Qry3 is Members and Qry2. No dice.

In each case, as soon as Members and Addresses get
together (in whatever form) the records are locked.

I've tried them with and without table relationships
turned on.

Beating my head on the wall on this one. I keep thinking
I'm missing a simple solution, but I obviously haven't
seen it yet.

 

[John Vinson]

OK, I've created an "active address" query (Qry1)and
verified that it only returns one address per household.
Qry2 is Member and Household tables plus Qry1. All three
are linked by household_ID. No dice, still record
locking.

Please post the SQL of qry1 and qry2. If qry1 is a Totals query it
will fail, but there should be a way around it... which I can't
suggest because I can't see the queries.

 

[Ron]

Qry1:
SELECT tbl_Addresses.Address_auto, tbl_Addresses.Street,
tbl_Addresses.City, tbl_Addresses.State,
tbl_Addresses.Zip, tbl_Addresses.Household,
tbl_Addresses.Del_Address, tbl_Addresses.Active_Address,
tbl_Addresses.Address_Comment, tbl_Addresses.User,
tbl_Addresses.Date_Entered
FROM tbl_Addresses
WHERE (((tbl_Addresses.Del_Address)=No) AND
((tbl_Addresses.Active_Address)=Yes));
- - - - - - - - - - - - - - - - - - - - - - -
Qry2 (Household and Member table and Qry1):
SELECT tbl_Members.REMOVE, tbl_Members.FNAME,
tbl_Members.LNAME, tbl_Households.Household_ID,
qry_Addresses_Active.Street, qry_Addresses_Active.City,
qry_Addresses_Active.State, qry_Addresses_Active.Zip
FROM (tbl_Households INNER JOIN tbl_Members ON
tbl_Households.Household_ID = tbl_Members.HOUSEHOLD_ID)
INNER JOIN qry_Addresses_Active ON
tbl_Households.Household_ID =
qry_Addresses_Active.Household;
- - - - - - - - - - - - - - - - - - - - - - -

The data displays correctly, but the records are locked.
Yes, there is a listing for each member of the household,
but that is OK. The users won't be changing address
information here, just member info.

Any ideas? I might be overlooking something obvious.