Query combining multiple records from one table can't add records

[Clint Marshall]

I have two tables - tblMember and tblAddress. tblAddress has two records
for every person in tblMember - a summer address and a winter address
(denoted by S and W in the "season" field of tblAddress). tblMember has an
Autonumber field for its primary key (MemberID). It is linked with
referential integrity to tblAddress. The key in tblAddress is a combination
of MemberID and the season field (either an S or a W).

The goal is to combine data from tblMember with BOTH the appropriate S and W
records of tblAddress in a query so that I can show it all on one form for
updating and adding new members. The only way I've been able to make this
work is to create what I call subqueries sqrySummer and sqryWinter and then
combine the results of these queries with the data in tblMember in a third
query called qryMemberWAddress. This had some troubles early on that I
could only resolve by making the Recordset Type be "Dynaset (Inconsistent
Updates)".

Now I find that I can update any addresses that already exist, but when I
try to add a new member through qryMemberWAddress or the form based on it, I
get an error stating that "You cannot add or change a record because a
related record is required in table tblMember". If I only fill in the
fields that come from tblMember, Access will accept the record. As soon as
I try to enter anything into the fields that come from tblAddress, I get the
message. It would appear that the query knows how to add the record in
tblMember, but doesn't know how to add the records in tblAddress. Even if I
set up the record in tblMember and then try to use the query to enter
data/add records to tblAddress, I get the same error.

I presume this problem originates with trying to pull and combine data from
two records in the same table (tblAddress), but don't know how to fix it.
Should the table structure be set up differently, or is there something I
can do in the query or coding to make it work?

Thanks!

-Clint Marshall

 

[Jeff Boyce]

Clint

It isn't clear to me, from your description, why you need a separate table
for addresses. Yes, a fully normalized design would use an address table,
but since more than one person could "use" the same address, you'd need a
total of three tables to more fully normalize the design.

Instead, why couldn't you put summer and winter address fields in the
tblMember (I can hear the database/normalization/design bigots shrieking ...
and I AM ONE!)?

Or are there other considerations not included in your description?

 

[GVaught]

In order to relate the tblAddress with the tblMembers table the tblAddress
should have its own primary key and then add MemberId as a foreign key.
Eliminate the combokey with memberID and Sor W. Then set the referential
integrity between MemberID Members table to MemberID Address table. Another
method is to add just the MemberID in the Address table and don't set it as
a primary key and then set up referential integrity. You will still get a
one to many relationship. This eliminates the need of your combokey or
separate primary key.

In your tblAddress create a separate field to contain either S or W. This
will solve your problem with updating and adding new members, as you can
create a Main from (Members table) and a subform(Address table). When you
call up a member, if they have two address they will show up in your form.

 

[Clint Marshall]

Jeff-
I guess I'm primarily using the address table for normalization purposes,
not for any programming logic. It has long occurred to me that I could also
have an address table with separate fields for summer and winter data and
that might solve my problems.
What's the logical way to proceed?
-Clint

 

[Jeff Boyce]

Clint

My response, although perhaps not clear, was to suggest that you don't need
an Address table at all.