Filter main table by data contained in related table.

Hi,

I am working on a simple database based on football just to get some experience.

I have a 'Main' table that contains the main information for the game [Game ID](Primary key), [Game Date], [Opponent], [Result] etc.

I also have another table 'Players' related by [Game ID] to 'Main' that contains the player names for each game.

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 'Players' subform so I can see and enter the players for that particular game. (Each player is a new record in the subform, usually 11 records per Game ID ).


In the header of the 'Main' continuous 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
Else
.FilterOn = False
End If
.Requery
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 related table for those games.

i.e. only show records/games where Joe Bloggs was in the team lineup.

I have tried creating a query that contains both the 'main' table and the 'players' table (Inner join) it works in that it will filter only those records containing Joe Bloggs but when the filter is clear it gives 11 entries per game e.g.

Game ID: 1 Date: 10/8/2010 Player: Joe Blogs
Game ID: 1 Date: 10/8/2010 Player: Dave Smith
.
.
.
Game ID: 2 Date: 17/8/2010 Player: Joe Blogs
Game ID: 2 Date: 17/8/2010 Player: Dave Smith

but I only want to see one record per game ID.


Any ideas please?

I reckon you probably just need to make your query a SELECT DISTINCT query instead of a plain SELECT query.

hard to be picture what you have but you may just need to reverse the where clause so you use
where [form control] = fieldname or is null

try moving the [forms].[fixtures].[filter7] to the field area in place of players and then in the criteria use
players.player or is null

you're welcome :-)