About Rob Henderson Expertise I can answer most MS Access design questions. I also welcome questions on database design and implementation and VBA programming questions.
I also have expierence in application design for all the Office components (Excel, Outlook, etc).
Question But ACTORS can also have one or more DVD
Thanks
-------------------------
Followup To
Question -
Rob,
I'm sorry, I didn't really understood what you meant. I think I did something like you said (but I didn't use a subform for it) with the director field.
The problem I had with actors is because two movies can have different numbers of main actors (one movie with 2 another with 6 for example). If I don't use the Cast table, I dodn't really know how to set up a relationship between the Actor and DVD Collection (many to many maybe)?
Thanks
-------------------------
Followup To
Question -
Hi,
The reason I put ON DVD with more than ONE Cast is because of the table structure. The Cast table has only three fields: CastID, CollectionID and ActorID. But a movie has more than one actor, so I thought that to link, for example, 2 actors to a movie, I would need 2 lines in the Cast table, one to each actor linked to that movie.
Maybe my way of thinking is wrong. Is there another way I can do this?
Thanks
-------------------------
Followup To
Question -
Yes I did, one to many between Actors and Cast, and one to many between DVD Collection and Cast.
Thanks
-------------------------
Followup To
Question -
Hi Rob,
I trying to create a database to input all DVDs I have. I having lots of trouble trying to relate the actors table to the DVD table.
For this I created 3 tables:
DVD Colection - CollectionID, and other fields
Cast - CastID, CollectionID, ActorID, no more fields
Actors - ActorID, Actor, no more fields
Then I created a subform, inside the DVD Collection Form, so I could visualize the actors names. However, I have not been able to relate the DVD Collection and Actors table through the Cast table, so on the DVD Collection form I can only see tha CastID field. How do I do that?
Thanks,
Ruy
Answer -
Hi
Before creating the forms did you specify the relationships?
Answer -
Hi Ruy
From what I can see your relationships should look like this...
One DVD can have ONE CAST
One CAST can have one or more ACTORS
Is this correct? Can a DVD have more than one CAST?
rob
Answer -
Hi
If you're flexible I believe we can break this down a bit more.
What about dropping the CAST table and having one DVD has many ACTORS (after all, the actors related to the DVD are the CAST - I.e. CAST is just a group of actors which you have anyway with a direct relationship to the DVD's).
Create a main form based on your DVD'S and create a subform based on your ACTORS.
This way when you browse the DVD titles you'll see all associated ACTORS.
I don't know if this will work for you but the CAST table seems to be complicating your design.
Does that make sense?
rob
Answer -
Hi
I understand your need for CAST but is it not true that one DVD can have one or more ACTORS?
If so, you do not need the CAST table as the DVD is in a one to many with the ACTORS.
Any clearer?
rob
Answer This may be true but the design will still work with a one to many from DVD to ACTORS
You must have a starting point when designing a relational database and I seen this as DVD
If you had 5 DVD's and multiple ACTORS who have participated in all the DVD's you could still get the ACTORS in volved in the DVD's from a one to many. You could also get the DVD's from the ACTORS.
My own thoughts here are that functionaly you'll get everything you need from a one to many.
Is there any reason why this wouldn't work for you I.e. Is there anything specific in the data that you do not think you can get to by using a one to many.
