Really need help with this!!!!!!

[weedot]

This probably exists elswhere on the forum but cant find itso any help will be great.
Want to select a list of players from the players table who arent in a selected team for a selected competition. For example there are x number of females in the players table, and as each is entered in a team, I want them to disappear from the select query on a per competition basis. Query is as follows:

SELECT Players.Name, Players.Sex
FROM Teams, Players
WHERE (((Players.Sex)="F") AND ((Teams.Compnum)=[Forms]![Ladies Teams]![txtCompnum]) AND ((Exists (SELECT Teams.Skip, Teams.Lead, Teams.Second, Teams.Third, Teams.Compnum From Teams where players.name=teams.skip or players.name=teams.second or players.name=teams.third or players.name=teams.lead))=False));

Works ok apart from the compnum. As soon as a person enters ANY comp the query drops them irrespective of which competition they have entered.

Any help would be gratefully recieved!

 

[gemma-the-husky]

in general terms, you probably need to do this in reverse (you may already be doing this)

a) do a query that returns the players who ARE involved (either a totals query, or a unique values query)

b) if necessary do another query to select the players you are interested in (eg ALL the females)

c) then do an unmatched query bewteen b) and a)

--------
just easier to folow if you do it parts

 

[weedot]

Thank you

Thanks GTH. Made some changes based on your advice and form is working a treat now.