Unique values in Record Source for Main form

[debbieg]

I have created a database for a senior softball league.

I have a main form and subform which is working great to display players on team by year and season (e.g., 2008 Fall).

The main form is based on a query.

SELECT Members.MemNum, Members.LN, Members.FN, Members.Nickname, Members.Suffix, Members.Gender, Members.BirthDate, Members.Died, Teams.TeamYear, Teams.TeamSeason
FROM Teams INNER JOIN (Members INNER JOIN Players ON Members.MemNum = Players.MemNum) ON Teams.TeamNum = Players.TeamNum
WHERE (((Members.Died) Is Null) AND ((Teams.TeamYear)=[forms]![frmPlayers&Teams]![GetYear]) AND ((Teams.TeamSeason)=[forms]![frmPlayers&Teams]![GetSeason]))
ORDER BY Members.LN, Members.FN;

The main form has a combobox based on a table to select the year and season. Then, another combobox based on the same query as the main form to select the players in that year and season. The subform displays the team(s) the selected player was on.

SELECT Players.MemNum, Players.TeamNum, Teams.NightPlayed, Teams.TeamName, IIf(IsNull([Nickname])=True,[FN],[Nickname]) & " " & [LN] AS ManagerName, Teams.TeamYear, Teams.TeamSeason
FROM (Members INNER JOIN Teams ON Members.MemNum = Teams.ManagerMemNum) INNER JOIN Players ON Teams.TeamNum = Players.TeamNum
WHERE (((Teams.TeamYear)=[forms]![frmPlayers&Teams]![GetYear]) AND ((Teams.TeamSeason)=[forms]![frmPlayers&Teams]![GetSeason]))
ORDER BY Teams.NightPlayed, Teams.TeamYear DESC , Teams.TeamSeason;

My problem is ... a player can play on more than one team during a season (on different days). I want the record count on the main form to count a player once. If I change Unique Values to Yes in the main form's query, my main form is blank when I open it. I have Unique Values set to Yes in the combobox but that doesn't affect the record count.

I'm probably missing something very obvious but I'm not seeing it.

I hope this makes sense.

Thanks in advance for any help,

Debbie

 

[debbieg]

Maybe I didn't get an answer because I didn't explain my tables.

Members
MemNum (Primary Key) (Auto Number)
Gender
FN
MiddleName
LN
Suffix
Nickname
BirthDate
Died
etc., etc.

Teams
TeamNum (Primary Key) (Auto Number)
ManagerMemNum (Foreign Key)
Gender
TeamName
TeamYear
TeamSeason
NightPlayed

Players
MemNum (Primary Key) (Foreign Key)
TeamNum (Primary Key) (Foreign Key)

My Main Form gets the players in a specific year and season (e.g., 2008 Fall) ... I get the year and season from a combobox on the Main Form (based on the Teams table). Then I have another combox on the Main that lets the user select a player from that year and season. Then the Sub Form displays the team(s) the player was on during that year and season.

I want the record count in the Main Form to count unique players. For example, in 2008 Spring we had 225 players but the count is 314 because some players played on 2 or more nights a week.

Is it possible to get what I want? If I change Unique Values to Yes in the Main Form's query, the Main Form is blank (no controls) when I open it.

Thanks for any help.

Debbie