title: SQL: SELECT COUNT involving a couple of tables
body: <p>I have a small MS Access database containing four tables and are struggeling with a SELECT COUNT query which involves all tables. Any help will be greatly appreciated.</p>
  
  <p>Overview of the database:</p>
  
  <pre><code>TABLE       COLUMNS
  
  Person         Person_ID |....
  Games          Game_ID | Name |....
  Played_Games   Played_Games_ID | Game_ID |....
  Participation  Played_Games_ID | Person_ID |...
  </code></pre>
  
  <p>To list out all games and how many times each game is played in total I use:</p>
  
  <pre><code>SELECT DISTINCT
  Games.Name,
  (SELECT COUNT(*) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS TimesPlayed
  FROM Games
  ORDER BY Games.Name
  </code></pre>
  
  <p>This works great, but I would also like to list how many times each game is played by members of a certain group of persons</p>
  
  <p>I have made some progress with the code below (line 4), but I'm still stuck with one small problem:</p>
  
  <p>The reference to Games.Game_ID at the back of the innermost brackets is not linked to the current Game_ID anymore, as it is in the code above. It now seems to be undefined. When I run this within MS Access Query Designer to test, it asks me to type in a value for Games.Game_ID. If I then type in some random Game_ID the code runs "perfectly", but as you probably already figured out, the value of TimesPlayedByGroupMembers will then be the same for every row in the recordset and only true for that one particular Game_ID that I typed in</p>
  
  <pre><code>SELECT DISTINCT
  Games.Name,
  (SELECT COUNT(*) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS TimesPlayed,
  (SELECT COUNT(*) FROM (SELECT DISTINCT Played_Games_ID FROM Participation WHERE Played_Games_ID IN (SELECT Played_Games_ID FROM Played_Games WHERE Game_ID = Games.Game_ID) AND Person_ID IN (26, 27, 28))) AS TimesPlayedByGroupMembers
  FROM Games
  ORDER BY Games.Name
  </code></pre>
  
  <p>I will try to explain what my code does and/or is supposed to do from the inside out</p>
  
  <p>The innermost brackets lists out Played_Games_ID's that involves the current game, but this doesn't work because Games.Game_ID is not linked to the current Game_ID</p>
  
  <p>The middle brackets (including the one above) lists out Played_Games_ID's that involves the current game and one or more of the selected persons. The Person_ID's listed here are just an example, and this part of the code is working</p>
  
  <p>The outermost brackets (including all above) counts how many times each game is played by one or more of the selected persons</p>
  
  <p>I'm really stuck with this, so any help will be greatly appreciated</p>
  
  <p>Thanks for your time</p>