help counting distinct values

[joo-joo-man]

Hi there,

I'm a total beginner at SQL and Access and I'm having trouble writing a query. Hopefully somebody will steer me in the right direction and I will learn something in the process.

I am working with a Chess database that tracks clubs, players, tournaments etc. I need to write a query to extract the player id, name, number of tournaments played in and the number of placings (1st, 2nd, 3rd) won for each player.

There are usually different events at each tournament, so a player can technically gain more than one placing at a single tournament (e.g. 1st in one event, 2nd in another for example).

Therefore, I want to extract only the unique number of tournaments a player has entered, and the total number of placings they have achieved (e.g. Bob Smith entered 3 tournaments and achieved 5 total placings).

The way I have my query set up now (see below), it would extract 5 tournaments entered and 5 total placings achieved for Bob Smith. My question is, how can I make my query only select the distinct number of tournaments and placings?


SELECT Player.Player_ID AS ID, (Player_lname + ", " + Player_fname) AS NAME, COUNT(Tournament_ID) AS numOfTournaments, COUNT(Placing_ID) AS numOfPlacings
FROM Player, Representative, Contestant, Placing
WHERE Player.Player_ID = Representative.Player_ID AND Representative.Rep_ID = Contestant.Rep_ID AND Contestant.Cont_ID = Placing.Cont_ID
GROUP BY Player.Player_ID, Player_fname, Player_lname
ORDER BY COUNT(Placing_ID) DESC;

[slave138]

I'm not exactly sure if this is what you're looking for but it might be a useful function for getting a distinct count:

ECount

[joo-joo-man]

Thanks for the reply, but I still haven't figured it out