views:

40

answers:

0

First, I am a noob, just trying to learn some Access/VBA/SQL, but I have been stumped by this issue. Shown below are the (9) tbl JOIN relations.

This lists X records for each game_ID (X = number of players in game). I only want one record per game_ID where

  • [game_players.player_ID] is a selected/special player (say, HERO )
  • [game_players_1.player_ID] is WINNER (who is NOT HERO, i.e. VILLAIN )

I am attempting to select ::: where

[game players_2.player_ID] is 2nd BEST Villain

(if there is one, otherwise, NULL-field)

I am trying to replace [game_players_2] with a DERIVED-TABLE for an OUTER JOIN because, when I use the limiting subquery (shown below) in WHERE clause (or QBE : criteria for [game_players_2.player_ID]) the OUTER JOIN behaves like INNER JOIN (i.e. only records with 2 or more Villain WINNERS are displayed, records with only (1) villain records are NOT output by query.

If you need more details/code, let me know....

================= JOIN RELATIONS ====================

FROM players AS players_2 
  RIGHT JOIN (game_players AS game_players_2 
    RIGHT JOIN (players 
       INNER JOIN (game 
          INNER JOIN ([session] 
             INNER JOIN (HandCalcs 
                INNER JOIN (HandCalcs AS HandCalcs_1 
                   INNER JOIN (game_players AS game_players_1 
                      INNER JOIN game_players 
                   ON game_players_1.game_id = game_players.game_id) 
                ON (HandCalcs_1.player_ID = game_players_1.player_id) AND 
                   (HandCalcs_1.game_ID = game_players_1.game_id)) 
             ON (HandCalcs.player_ID = game_players.player_id) AND 
                (HandCalcs.game_ID = game_players.game_id)) 
          ON session.session_id = game_players.session_id) 
       ON game.game_id = game_players.game_id) 
    ON players.player_id = game_players_1.player_id) 
  ON game_players_2.game_id = game_players.game_id) 
ON players_2.player_id = game_players_2.player_id

============== subquery / (? defined table )  ====================

( SELECT TOP 1 DUP2.player_id
          FROM HandCalcs AS DUP2
           WHERE (
                        ((DUP2.player_id)<>[Forms]![Combo2]![cboName_PID]) AND 
                        ((DUP2.game_id)=(game_players.game_id)) AND
                        ((DUP2.player_ID) <> ( game_players_1.player_id)) AND
                        ((DUP2.HV_CatctusKev_R) Is not Null)
                  )
          ORDER BY DUP2.HV_CatctusKev_R ASC , DUP2.player_id 
 )

================================================================