Having trouble getting data to display right

[John F]

I have set up an inventory of my video collection and I am unable to get the
responce from the data that I am looking for. The tables have the following
primary keys and the following many-to-many relationships set up.
Table A Table B Table C
VideoCollectionID VideoColToPepID VideoPeopleID
VideoCollectionID
VideoPeopleID
Using all three tables in my forms with subforms when I open up the Video
Collection form I can see all of the actors and actresses. However when I
open up the Video People form I can only see one film that they were in.

The question how can I see all of the films that they are in???????????????
That is without scrolling thru the data one entry at a time. I'm stuck.

 

[Duane Hookom]

Generally, you would display the Table B records in a continuous subform.

 

[John F]

If you are saying that on the top of the form displays the actors name and at
the bottom of the form (sub form) should be a data table of films that this
actor is in then here lies the problem. The first line shows one film and the
second line shows autonumber and not the other 5 film names in the test data
since this actor is in all 6 films.

 

[mnature]

Set up a query based on Table B, using all the fields in that table. In
addition, use the data (not the ID's) from Table A and Table C.

Now use the wizard to create a form based on that query. After you choose
the query, you will be asked how you want to view your data. Select by
Actors (or People). This will give you a main form that just has
Actor/People, and a subform that shows the movie ID's and names. You can
then clean up the main and subforms to show just what you want.

 

[John F]

I get the same results. Each actor shows only one mivie. While each movie
shows 2 actors.

I am now starting to wonder if it has something to do with the way I have
input the data. I am using table a as my primary section in a form with table
b and c in the subform. Using this form later it shows every actor for the
particular film which is displayed. However when I use table b as the primary
section and table a as the subform I get only one film that the actor was in
instead of 6. Table b is only used in an effort to create the many-to-many
relationship between table a and c.

The only information in table b is a 3 way primary key.
VideoPeopleID Autonumber
VideoCollectionID Number infinite to one to VideoCollectionID in table a
VideoPeopleID Number infinite to one to VideoPeopleID in table c

The next question is inputting 6 films with 2 actors each result in 6
entries in table a
and 12 entries in both tables b and c????

 

[mnature]

Oh, in addition, your Table A and Table C should be in a one-to-many
relationship with Table B, not a many-to-many relationship.

 

[mnature]

Table A should only have the names of movies. Table C should only have the
names of people. Table B will only have its own primary key, and the two
foreign keys for matching up the movie table with the actor table.

So, Table A has Movie1, Movie2, Movie3, Movie4, Movie5, and Movie6, and the
data looks like (VideoCollectionID, MovieName)
1, Movie1
2, Movie2

Table C has Actor1 and Actor2, and the data looks like (VideoPeopleID,
PeopleName)
1, Actor1
2, Actor2

Both actors appear in all of the movies.

Table B should have (VideoColToPepID, VideoCollectionID, VideoPeopleID)
1, 1, 1
2, 1, 2
3, 2, 1
4, 2, 2
5, 3, 1
6, 3, 2
7, 4, 1
8, 4, 2
9, 5, 1
10, 5, 2
11, 6, 1
12, 6, 2

Is this how your data looks?

 

[John F]

No the data is different.
Table A is the same.
1, Movie1
2, Movie2
3, Movie3 ECT.

Table B is different
1, 1, 1
1, 1, 2
3, 2, 3
3, 2, 4 ECT.

Table c is different??
1, Actor1
2, Actor2
3, Actor1
4, Actor2 ECT.

The infinity sign is at table b, a 1 at table a, and a 1 at table c.

 

[John F]

Sorry made mistake.
Table B is different
1, 1, 1
1, 1, 2
3, 2, 3
3, 2, 4 ECT. Wrong entries.

It should be.
Table B is different
1, 1, 1
2, 1, 2
3, 2, 3
4, 2, 4 ECT.

 

[BruceM]

A typical way in which this is handled at the user interface level (once the
tables and relationships are set up as specified, which you seem to have
done correctly) is to have a main form based on tblVideo, and a subform
based on tblVidToPep. There would be on the subform a combo box bound to
PeopleID. The combo box row source is tblPeople. PeopleID is the Control
Source for the combo box. PoepleID (from tblPeople) needs to be the combo
box's bound column.
Keep in mind that you need to create the record in tblPeople before you can
select the name from the combo box. Each performer's name appears once (no
more)in tblPeople.

 

[mnature]

Your tables are not normalized. There should not be any duplicate entries in
your tables. If you only have two actors, there should only be two entries
in your Table C.

 

[John F]

Thanks for everyones help. Between you all the solution jumped out and
smacked me in the head. Now I feel really stupid.

 

[John F]

Succes just the right nudge in the correct brain cell. Thanks.

 

[John F]

Thanks just the right nudge in the correct brain cell. Thanks

 

[BruceM]

Nobody was born knowing this stuff, and it's not exactly intuitive when you
first start working with it. Good luck with the project.