If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query combining multiple records from one table can't add records
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 |
#2
|
|||
|
|||
Query combining multiple records from one table can't add records
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? -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
Query combining multiple records from one table can't add records
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. -- G Vaught "Clint Marshall" wrote in message ... 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 |
#4
|
|||
|
|||
Query combining multiple records from one table can't add records
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" -DISCARD_HYPHEN_TO_END wrote in message ... 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? -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
Query combining multiple records from one table can't add records
Clint
My response, although perhaps not clear, was to suggest that you don't need an Address table at all. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
append Query duplicating records | Alex | Running & Setting Up Queries | 1 | July 8th, 2004 01:31 PM |
can't append records in append query | Greg Clements | Running & Setting Up Queries | 1 | July 2nd, 2004 04:29 PM |
I can see the data but... | David F-B | General Discussion | 3 | June 24th, 2004 06:15 AM |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |