Can't add records to statement with two many-to-many joins.

[Peter Stone]

Access 2003, XP Pro

I have a table of Destinations. Destinations can have more than one Name
(e.g., common name, official name, previous name, alternate name (e.g.,
Holland/Netherlands). Some Destinations have an Article (e.g., THE
Netherlands, THE United States). The Article is more complex in other
languages--Spanish has four articles: El, La, Los, Las.

The problem is that to join the Destination to the Name and then join the
Name to the Article requires two many-to-many joins in the SQL statement.

I can't add records to the SQL below.

SELECT tjnDestinationName.DestinationID, tjnDestinationName.NameID,
tblName.Name, tjnNameArticle.ArticleID
FROM tlkArticle INNER JOIN ((tblName INNER JOIN tjnDestinationName ON
tblName.NameID = tjnDestinationName.NameID) INNER JOIN tjnNameArticle ON
tblName.NameID = tjnNameArticle.NameID) ON tlkArticle.ArticleID =
tjnNameArticle.ArticleID
WHERE (((tblName.NameCatID)=3) AND ((tlkArticle.LanguageID)=1));

I could have a separate form that is based on NameID and add the Articles
there, but it's not neat.

FYI: The tables above that are prefixed tjn are the linking tables.

Thank you

Peter

 

[Jeanette Cunningham]

Hi Peter,
For a discussion of the reasons and some ideas, go to

http://www.allenbrowne.com/ser-61.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Peter Stone]

Thanks Jeanette.
I'm 99% sure I can't add records because the query contains 2 link tables to
create many-to-many joins. Allen's website says why, but provides no
solution. I'm hoping someone can suggest a way around my problem.

The query drives a continuous subform. I can divide the query into two
halves, but I see no way of putting the two halves into subforms that will
connect.

 

[Jeanette Cunningham]

The way round the problem is to create different forms and subforms.
It is usually easier if you keep to the idea of 1 or at most 2 tables for
each form for data entry forms.
You side step around the many to many joins by using subforms that relate
one table to the other table using the junction table.
You can base the parent form on the one side of a relationship.
Base the subform on the junction table.
Use a combo based on the table on the other side of the junction table. The
combo goes on the subform.

To summarise, to do data entry for many to many relationships, pick the 2
tables and their junction table to create a form, subform and combo setup
for data entry.

If you have a second many to many relationship, build a second set, using
the second set of tables, with one parent form, a subform based on the
junction table and a combo based on the other table involved in the many to
many relationship.




Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia