I am trying to create a database where some of the data is related to movies. I would like to return information on a given movie that displays a undefined number of cast members.
Also, if i can ask another question, can I define a field that only returns two options, like, Movie or TV Show.
Also, can a button be used to display the cast members, only when pressed.
And what is the terminology used if I want the data input from different forms. Am I to create a different database or a different table. I want the cast member records stored separate from the movie records, but linked by a common field.
I would like to return information on a given movie that displays a undefined number of cast members.
This would be a many-to-many (m-n) relationship between two tables. In a relational database this is implemented by a separate table linking to either of the two primary tables.
Movie(ID, Title, Year, ... )
Person(ID, Name, ...)
MovieActor(MovieID, PersonID)
The main table for a form will often be that “secondary” table, where movie and person data are in “dependent” tables and hence belonging in subforms. (More about subforms below.)
a field that only returns two options
The “traditional” approach to this is to use a separate table for the options and link the value (directly so the actual option is stored in the main table, or by way of an ID field). In a form you need to use a subform for this. A subform in this context is not a “separate canvas” but only a way to conceptually group fields (using the form navigator) so they are seen as “dependent”.
Some database tools will offer a more direct approach, but in my experience that makes the end product less portable. I never used it myself, but have come across a good number of cases with MS Access databases which use this, and are unmanageable by any other product that MS Access.
… terminology used if I want the data input from different forms.
Simply that, “different form”. You can make multiple forms connected to the same set of data.
I want the cast member records stored separate from the movie records, but linked by a common field.
This is a good idea, and indeed a step towards what I explained for the first question. Since an actor can participate in multiple productions, and any one production will have multiple participants, you cannot have a single field in either table to point to the other. This is the simple definition of a m-n relationship and the reason why you need the third table.
Thanks for your help.