Many to one relationship question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table (A) with many-to-one relationships with two other tables (B &
C). My joining fields in A are foreign fields; however, I am joining to
primary fields in B & C. The join properties are set correctly. That is, I
want all the fields in A and only matching fields in B & C. In both cases
(the A-B join and the A-C join) the resulting queries are correct. However, I
am not able to add records to the A table in the A-B join but can in the A-C
join.

I see no difference in my logic between the two joins. Can anyone set me
straight?
 
Well, the most common reason for not being able to add records to a
recordset that joins two tables is because the field in the primary table
does not have a unique index. Even though you said it's true, check again
to make sure the field in table B is a Primary key.

The next most common problem is adding the wrong field to the query. In the
query, you need to have the join field from table A, NOT table B.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks for your amazing quick reply. I checked out both your suggestions.
I'm definitely joining to primary keys (they are in bold) and have both the A
foreign key and the C primary key in the query.
 
Amazingly quick, yet amazingly unhelpful! LOL

I'm stumped based on the information you've given. There are other reasons
why a recordset might not be updateable. Open Help, and in the Answer
Wizard tab type:
when can I update data from a query
This will give you a laundry list of situations that can cause
non-updateable recordsets. See if one of those fits your situation.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Help was not helpful but it did prompt me to examine the property sheets for
all three queries. They looked exactly the same so I've checked off another
blind alley. Guess I'm going to have to start slogging thru my Sybex and Que
manuals. I'll let you know if and when the light dawns.
 
Back
Top