Help with a QUERY

[Ashlewis]

Im running an access database.
Which looks like this:


Im struggling to make a query to do exactly what i need.

Firstly i'll put it into context. Im allowing users to sign up to play in a sport, and i want to return a list of all the sports that are available to play in.
therefore...
I need to select all the sportName from TblSport.

However, i have a few other constraints i need to include:
A sport should only be selected if it has a league that has exactly ONE season stored in the TblLeagueSeason .

Also the size attribute in TblLeague specifies the maximum number of teams that can play in it.
A sport should only be Selected if the season has less than the max number of records (size) stored in TblLeagueSeason Team

So ive got as far as saying:

Select sportName FROM TblSport WHERE "number of seasons for a league in that sport = 1" AND "Number of teams for that season < size of its sport"

Any Help would be greatly appreiated!
Thanks! Ash

[Delerna]

Try this one, I think it will do what you want.
[code=sql]
SELECT tblSport.SportN ame,
tblLeague.Leagu eName,
tblLeague.Size AS MaxTeams,
z.NumTeams
FROM
( SELECT LeagueID,count( LeagueID) as NumSeasons
FROM tblLeagueSeason
GROUP BY LeagueID
) AS y

INNER JOIN
( SELECT a.LeagueID,Coun t(b.TeamID) AS NumTeams
FROM tblLeagueSeason AS a
LEFT JOIN tblLeagueSeason Team AS b
ON a.LeagueSeasonI D = b.LeagueSeasonI D
GROUP BY a.LeagueID
) AS z ON y.LeagueID = z.LeagueID

INNER JOIN tblLeague ON y.LeagueID = tblLeague.Leagu eID
INNER JOIN tblSport ON tblLeague.Sport ID = tblSport.SportI D
WHERE z.NumTeams<[Size] AND y.NumSeasons=1;

[/code]

[Ashlewis]

Hi thanks alot! just spent some time getting my headaround it, i think im getting there.

Ive tried using it, i made some changes just to some table names, so it fits my database just simple things such as tblSport to TblSport

Now i have this:

SELECT TblSport.sportName,
      TblLeague.leagueName,
      TblLeague.size AS MaxTeams,
      z.NumTeams
FROM
(    SELECT leagueID,count(leagueID) AS NumSeasons
      FROM TblLeagueSeason
      GROUP BY leagueID
)  AS y

INNER JOIN
(    SELECT  a.leagueID,Count(b.teamID) AS NumTeams
      FROM TblLeagueSeason AS a
      LEFT JOIN TblLeagueSeasonTeam AS b
                ON a.leagueSeasonID = b.leagueSeasonID
      GROUP BY a.leagueID
) AS z ON y.leagueID = z.leagueID
  
INNER JOIN TblLeague ON y.leagueID = TblLeague.leagueID
INNER JOIN TblSport ON TblLeague.sportID = TblSport.sportID
  
WHERE z.NumTeams<[size] AND y.NumSeasons=1;

however when I try saving it as a query in access i get this error



Any ideas?

[Delerna]

Can't see anything obvious and without being able to get my hands on it and try different things it can be difficult to discover these errors.
I wrote the entire thing as a single query and I wrote it on a mock up of your table schema so it works without error.
In reality there are 3 queries in my solution. 2 subqueries for the aggregate fields, and then joining them to the other two tables to get the result you needed.

So you could break the query up into 3 separate queries. I each for the 2 subqueries and the third one to join them all together. That way you should be able to build it up piece by piece.
So this would be 1 query, perhaps called qryCountSeasons

[code=sql]
SELECT leagueID,count( leagueID) AS NumSeasons
FROM TblLeagueSeason
GROUP BY leagueID
[/code]

and this would be the second query, perhaps called qryCountTeams
[code=sql]
SELECT a.leagueID,Coun t(b.teamID) AS NumTeams
FROM TblLeagueSeason AS a
LEFT JOIN TblLeagueSeason Team AS b
ON a.leagueSeasonI D = b.leagueSeasonI D
GROUP BY a.leagueID
[/code]

then all you need do is use query designer to connect the two tables and the two queries together for your final result.

There is nothing wrong with doing it like that. I don't like to because there are two extra queries in the query window that are not being used by anything other than a single query and they can be removed from the list by using subqueries in the final result query. Just makes it less confusing for me in 6 months time when i've forgotten what all 150 + queries in the display do