One To Many To One Common Problem

[Paul B]

This seems to be a perrenial problem with many database designs.
Using a simple example of a movie database (but I have other
examples), one MOVIE belongs in one to many GENREs, and one GENRE has
zero to many MOVIEs. So obviously there is a many to many
relationship. You break that up with an associative entity in the
middle, MOVIEGENRE, that contains the foreign key for MOVIE and the
foreign key for GENRE. So far so good. And if I open the table
MOVIEGENRE and type in MovieID and GenreID in a record, I can add as
many records as I want.

Of course that's not going to work for a real application because no
one is going to memorize all those keys. So you create a form for
Movie that is driven off the MOVIE table, and you see the title, run
time, rating, a nice combo box to select the Director (one DIRECTOR
directs one to many MOVIEs, at least in this system), and so forth.
Putting combo boxes for any kind of many (MOVIE) to one situation is
easy enough. If we agreed that a movie would only be in one GENRE,
there wouldn't be a problem.

But lots of applications have this kind of relationship. So I want to
put a subform on the Movie form that contains a grid (rather than a
multi-select combo box, which would be even more trouble) that shows
all the GENREs for the MOVIE. I've done that, and linked it through
MovieID, and it displays just fine. However, while I can start to add
a record, and I can in fact pull down a combo box on the grid line to
select, say, "Comedy", I cannot actually add the record.

The query that this is based on contains all the MOVIEGENRE records,
and just those from MOVIE and from GENRE that match. If it wasn't for
including the actual name of the Genre, I could just use the table and
not a query.

Now, in searching the archives here and elsewhere, everyone quotes the
Microsoft documentation about "How to edit records in related tables
in a Microsoft Access database" going back to the 90s, where it lists
the specific problem of a "query based on three or more tables in
which there is a many-to-one-to-many relationship". It then says
"Though you can't update the data in the query directly, you can
update the data in a form or data access page based on the query if
the form's RecordsetType property is set to Dynaset (Inconsistent
Updates)."

First, it doesn't even make sense to me that in this case a query
can't be updated directly, but if you put it in a form as stated you
can update it. So the answer to that might be interesting. But
second, and more importantly, it doesn't work for me. I realize that
there are a number of things I might be doing wrong (although I've
checked all sorts of things, adding referential integrity, dropping
it, etc.), and that no one is going to be able to debug this for me in
a forum, but surely SOMEONE out there has solved this problem,
probably repeatedly since this has to be a common issue for many
database applications. I'm thinking that a lot of people must have
struggled with this issue in their own applications. And I don't
think the answer is to redesign the application, since I'm positive
that the database design, from a data modeling perspective, is
correct. There must be SOME way to get multiple GENREs to show up and
be updatable for each MOVIE. Any suggestions? Thanks.

 

[Douglas J. Steele]

The normal approach is to use a form and subform. A query on table MOVIE
forms the recordsource for the parent form, while a query on table
MOVIEGENRE forms the recordsource for the subform. The subform's a
continuous form, where the Genre field is bound to a combo box that shows
all the rows in table GENRE.

 

[Paul B]

The normal approach is to use a form and subform. A query on table MOVIE
forms the recordsource for the parent form, while a query on table
MOVIEGENRE forms the recordsource for the subform. The subform's a
continuous form, where the Genre field is bound to a combo box that shows
all the rows in table GENRE.

That's almost (but clearly not) exactly what I had--a form based on
table MOVIE and a subform based on MOVIEGENRE. The subform that I
had, though, was a datasheet rather than a continuous form with a
combo box. And as it turned out, you were exactly right. Breaking
that extra join at the subform level to remove GENRE so that the
subform was only joining MOVIEGENRE to MOVIE on the main form, and
then looking up the Genre Name from GENRE was exactly the way to go.
I can't thank you enough for that insight! I hope someone else will
see this in the future and realize that it's the solution to a common
generic problem. THANKS AGAIN!