movie query

[mjpr54]

I have a db for movies that I own. I would like to make a query so that I could find movie title if I can remember several of actors in the movie. for example " what is the name of the movie with Paul Newman and Robert Redford" Any suggestions ?

 

[Beetle]

Assuming the db has a proper table structure for the many-to-many relationship that exists between Movies and Actors, then you would create a query of the relevant tables and restrict the results by applying criteria to the Actor column. The criteria could come from a form control (i.e. a text box, list box, etc.) where you enter or select the actors names.

Hard to be much more specific than that because we know nothing about the structure of your database. If you want to post back with more details someone can offer more detailed advice.

 

[mjpr54]

I believe many to many relationship between tables is set up correctly. I already have a form set up so I can query title by actor. the Query criteria looks like [forms]![formname]![comboboxname] And I have a button on the form to run the query.This allows me to select an actors name and the query returns all movies that have this actor in it. What I can't seem to figure out is how to run a query that searches title that contain actor A and actor B . hope this makes sense

 

[Beetle]

I would probably use a multi-select list box on my form to select one or more actors, then build a filter for the record set based on the selections in the list box. Post back if you need help with this. I can probably post a sample tomorrow if you need.

 

[mjpr54]

I think I can handle the multi-select list box part, but not sure about the filter part. Any help would be appreciated.

 

[Beetle]

I didn't have time to get to this yesterday, but I finished a sample file tonight. I'm attaching it here but I will also try to explain some of the logic. The sample has the following tables;

tblMovies
MovieID
MovieTitle
ReleaseDate

tblActors
ActorID
FirstName
LastName

tblMovieActors
MovieID
ActorID

We'll use the following data for the demonstration;

Movies
Butch Cassidy & The Sundance Kid
The Sting

Actors
Katharine Ross
Paul Newman
Robert Redford

So we have a list box on a form listing all actors where we can select which actors we want for the search. If we query for all three actors we want to return only the first movie. If we query for only Paul Newman and Robert Redford then we want to return both movies from the movies table.

If we create a query of all three tables and use the ActorID of these three actors as criteria, we get these results;

Obviously, this is not what we want, but on closer inspection is does give a clue as to how we can get to where we need. The number of rows returned for each movie is equal to the number of actors in the movie. So, we can use this as a sub query in another query that will limit the results to only those movies that are returned as many times as there are actors selected in the list box. In other words, we can count the number of times a movie is returned in the sub query and compare that to the number of selections in the list box.

Here is the code. If only one actor is selected in the list box, it just runs a simple query. If more than one actor is selected, then it builds the query/subquery to get the correct results. In either case, the results of the query are then applied to a filter.

Private Sub cmdSearch_Click()

    Dim intCount As Integer
    Dim lngActorID As Long
    Dim strActors As String
    Dim strFilter As String
    Dim strSQL As String
    Dim vItem As Variant
    
    'Determine how many Actors were selected
    intCount = Me.lstActors.ItemsSelected.Count
    
    If intCount = 0 Then 'No Actors selected
        MsgBox "Please select at least one Actor."
    Else
        Select Case intCount
            Case 1 '1 Actor selected
            
                'Return the ActorID
                For Each vItem In Me.lstActors.ItemsSelected
                    lngActorID = Me.lstActors.ItemData(vItem)
                Next
                
                'Simple query to return all Movies with this Actor
                strSQL = "SELECT tblMovies.MovieID FROM tblMovies " _
                          & "INNER JOIN tblMovieActors ON tblMovies.MovieID = tblMovieActors.MovieID " _
                          & "WHERE tblMovieActors.ActorID=" & lngActorID
                          
                'Place the results of the query in a filter
                strFilter = "MovieID In(" & strSQL & ")"
                
            Case Is > 1 'Multiple Actors selected
            
                'Build a comma separated string of ActorIDs
                For Each vItem In Me.lstActors.ItemsSelected
                    strActors = strActors & Me.lstActors.ItemData(vItem) & ", "
                Next
                
                'Remove the trailing comma & space
                strActors = Left(strActors, Len(strActors) - 2)
                
                'Query with a subquery to select movies where the Count of records
                'equals the number of Actors selected in the list box
                strSQL = "SELECT T.MovieID FROM " _
                       & "(SELECT tblMovies.MovieID FROM tblMovies " _
                       & "INNER JOIN tblMovieActors ON tblMovies.MovieID = tblMovieActors.MovieID " _
                       & "WHERE tblMovieActors.ActorID In (" & strActors & ")) AS T " _
                       & "GROUP BY T.MovieID " _
                       & "HAVING Count(T.MovieID)=" & intCount
                       
                'Place the results of the query in a filter
                strFilter = "MovieID In(" & strSQL & ")"
        End Select
        
        'Apply the filter to the form
        Me.Filter = strFilter
        Me.FilterOn = True
    End If

End Sub

See the attached sample file.