Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    5

    Question Unanswered: Unique Values for Record Source in Main Form

    I have created a database for a senior softball league.

    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 combobox 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. 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

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    As the form apparently deals with players in multiple roles (e.g. playing on different nights, as you have pointed out) the count in the form is not really related to the form at all, but is a convenience number attached to the form.
    You could create a listbox with one row and whose RowSource is a query of the type SELECT DISTINCT Count(MemNum) from a set of joined tables (as required) with parameters taken (again as required) from values in the form. In the latter instance, every time one of these parameters changes, you will need to refresh the RowSource.
    I hope this helps.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think your teams entity isn't quite correct
    a team exisits year after year
    the players on a specific teams are transient so, inmy books you need to separate the static data from the dynamic data.

    so I think you need to rethink that entity
    it may be that all you need to do is to push the year into the players entity, but there is a possible conflcit also wiith the teamseason..... what does that represent?
    can a team play on different nights?

    if 'all' you want to know is the number fo unique players woudl a separate COUNT of records do, using sa a DLOOKUP

    eg
    the sql is relatively trivial
    SELECT Count(UniquePlayers) AS NoPlayers
    FROM [select distinct(MemNum) as UniquePlayers from Players]. AS [playerquery];

    you may need to place his in a recordset as the expression may be too complex for dlookup
    effectively what we are doing is running two queries
    the innver query
    [select distinct(MemNum) as UniquePlayers from Players]. AS [playerquery];
    identifies unique players in Players using the distinct verb
    the outer query then COUNTS the number of rows returned from the inner query
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Posts
    5
    teamseason..... what does that represent? can a team play on different nights?
    TeamSeason is Summer, Spring, or Fall. Yes, a team can play on different nights during the same TeamYear.

    Oh, I forgot to mention that I have navigation buttons on my Main Form ... if we don't want to keep selecting players and just Next through them. If a player has more than one team listed, I have to hit Next twice to get to the next player.

    I'm leaving for work so I'll look at this later.

  5. #5
    Join Date
    Apr 2002
    Posts
    5
    Also, a team can play on different nights during the same TeamSeason.

    The query in my main form needs to gather the number of unique players that played in a certain season (e.g., Spring) in a certain year (e.g., 2008).

    A gal at work told me that she thinks having the ManagerMemNum in the Teams table is causing my problems but I don't understand how that can be.

    I either have a design problem or am wanting something that can't be done. The form and subform are doing what I want with the exception of my navigation buttons and the record count. I do without both but they would be nice to have.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    within reason you can do virtually anything in a SQL database providing you can envisage how the data is constructed.

    I suspect you have a design problem
    have a look at e the principles of normalisation
    Fundamentals of Relational Database Design -- r937.com
    and
    The Relational Data Model, Normalisation and effective Database Design
    are the oft quoted web references here

    the requirement
    Also, a team can play on different nights during the same TeamSeason.
    means there is a problem with your teams entity

    if a team can play on multiple days then the night played cannot be part of the team entity
    if a team has only one manager then it can remian in the team entity.
    if your team entity needs to store histroical associations of managers, players etc you need soem form of year/season, ideally as part of the PK.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •