List records not in a set

  • Thread starter Thread starter MikeB
  • Start date Start date
M

MikeB

As you may know from other posts I've made here, I'm playing with
Access to build a small chess management system.

As part of a draw for a match, one of the options to consider is to
match players that have not played one another previously.

To do this, I would like to have a form with a Combo box that is
populated with all the players that the current player have not yet
played (and, as a bonus, not their own record as well).

I can construct a query to list all the players that a particular
player has played before. How can I now construct a query and how do I
put this in the combo box criteria, such that I get a list of all
players that are NOT in the aforementioned list?

Thanks.
 
You can use a sunquery, but that concept may be a bit difficult to explain.
Access can use an existing query in a join, just as it uses a table. So ...

Use the query that you already have as a starting point. Then create a new
query using the players table joined to your query. Drag the PlayerID from
the query, and whatever data you need from the player's table. Now in the
criteria box, and add "Is Null" w/o the quotes.. Add the PlayerID from the
Players table and the expression: [Forms]![YourFormName]![YourPlayerID
textbox] (substituting your names, of course)

Now save this second query, and use it as the rowsource for your combo. In
the form's Current event, add the line of code:

Me.ComboBoxName.Requery

to requery the combo with each new player.
 
@b1g2000pra.googlegroups.com
:
As you may know from other posts I've made here, I'm playing with
Access to build a small chess management system.

As part of a draw for a match, one of the options to consider is
to match players that have not played one another previously.

To do this, I would like to have a form with a Combo box that is
populated with all the players that the current player have not
yet played (and, as a bonus, not their own record as well).

I can construct a query to list all the players that a particular
player has played before. How can I now construct a query and how
do I put this in the combo box criteria, such that I get a list of
all players that are NOT in the aforementioned list?

Thanks.
Click to expand...

The easiest technique is to use the query that contains those
opponents already met as a subquery against the list of all
opponents.

If your query of opponents previously played is called
qryPreviouslyMatched, and the ID of the team member you are matching
now is in the textbox MemberID, Something like

SELECT MemberName from Members
WHERE Members.MemberID
NOT IN
(SELECT MemberID as PotentialMatch from qryPreviouslyMatched)
AND
MemberID <> me.memberID.

would work
 
Back
Top