I am working on a simple database based on football just to get some experience. This website Latest Aston Villa Results, Fixtures & Betting Odds | Soccer Base is an example of what I am trying to achieve. Clicking onto the green (i) icon next to the game will bring up the team and there is a player filter that will show games played by that player.

I have a 'Main' table that contains [Game ID](Primary key), [Opponent], [Result] etc.

I also have a subform 'Players' related by [Game ID] to 'Main' that contains the player names for each game. This inputs into 'Players' Table

My 'Main' form is continuous giving me a list of all the games and I can click on one of the records which will open a 'Details' form containing the subform that shows the players for that particular game. (Each player is a new record in the subform, usually 11 per game id ).

In the header of the 'Main' form I have 4 combo boxes (filter1, filter2....) that can filter the records by fields contained within the [Main] table.

This is the code in the filter button:

Private Sub Command149_Click()
Dim varFilter As Variant
varFilter = Null

If Not IsNull(Me.filter1) Then
varFilter = (varFilter + " AND ") _
& "[seasonname]= '" & Me.filter1 & "'"
End If

If Not IsNull(Me.filter2) Then
varFilter = (varFilter + " AND ") _
& "[opponent]= '" & Me.filter2 & "'"
End If

If Not IsNull(Me.filter3) Then
varFilter = (varFilter + " AND ") _
& "[competition]= '" & Me.filter3 & "'"
End If

If Not IsNull(Me.Filter4) Then
varFilter = (varFilter + " AND ") _
& "[Result]= '" & Me.Filter4 & "'"
End If

With Me
If Not IsNull(varFilter) Then
.Filter = varFilter
.FilterOn = True
.FilterOn = False
End If
End With

End Sub

It works perfectly for filtering data contained within the 'main' table but I would like to add another filter that will show records where a certain players name is contained in the subform for those games.

I have tried setting the recordsource of the main form to a query that inner joins 'Main' and 'Players' but that will give me 11 records per Game ID. If I untick show for the player name field the query omits all the games that do not have any players added i.e. future games that have not been played but I still want to see those in the list.

Any ideas please?