Getting the opposite results from a query that is wanted

[madpiet]

I revised the Special_Groupings table to a new table with the name "Specials" as per your recommendation. I tried the code as you suggested and got no results. Query_New is what I've been working on. I've tried to simplify it to the best of my abilities to get some results to no avail. Your recommendation doesn't seem to take in the roster part of the equation.
Thank you for all your help

What does a "roster" mean in this case? A group of players that play together (analogous to a "team")? Just wondering so that I know what I'm talking about here.

and while I'm at it, what does "Special Groupings" even mean in everyday pedestrian English? First things first when describing database tables and objects - use the most obvious everyday words for column and table names. Nobody wants to spend all day guessing what your names mean. is "Special Groupings" like a team or something?

[MikeCt]

Maybe it would have been best it I started this way (I thought it would be easier for someone to show me how to get the opposite results, appears I was wrong with that line of thinking)

(Players) is a table of all players that have joined a senior golf league (around 145 players total)
(Roster_Temp_) is a temporary table of all players that signed up to play for a specific event (Date), hence the 04092025 (4/9/2025) at the end of the Roster_Temp_04092025
There're multiple events going on at one time. Once the event is held the temporary table is deleted (72 to 92 player per event)
(Special_Groupings) or (Specials) is a table of players that have asked to be grouped together for a specific date or for a time period of a few weeks (some time the whole season). It's a senior league, MANY demands
This table also contains players that need to be separated to help keep the peace, "Join_Seperate" Yes/No field
These specials can be 2 thru 4 players depending on the request.
(Join) selection: Player_1 is the Captain, Player_2 is a Gold Player with the 3rd and 4th player usually Red player. Hence why I was using Player_1, Player_2 ect
(Seperate) selection: Player_1 is the problem, 2 thru 4 are the players that have asked not to be team up with player_1 in the future

I'm going to try to attach some Pdf's
Captains_All all the captains in the league
Captains_Roster all the captains signed up for the event
Specials_Simple al the captains in the specials for a specific event with the Join selection
Specials_Roster same as Specials_Simple, but lists player only if there on the roster for the specific event

The list I'm trying to build is Captains_Roster with the players from Special_Roster removed from it. Yes, I could do this in other ways in the program, but this same scenario happens with 3 or 4 other tables and this seems the right way to go. I've been programming for 40 + years and I want to start understanding Sql better.

Hope this helps, Thanks for asking

[Welshgasman]

I thought it would be easier for someone to show me how to get the opposite results, appears I was wrong with that line of thinking)

You were shown that almost immediately.

[June7]

Definitely shown a solution in posts 6 and 15 which works with existing data structure (no response by OP). If you want to restrict output to captains then add that as more criteria in WHERE clause.

SELECT Players.*
FROM Players INNER JOIN Roster_Temp_04092025 ON Players.Player_ID = Roster_Temp_04092025.Player_ID
WHERE (((Players.Player_ID) Not In (SELECT [Player_ID] FROM SpecGrpUNION INNER JOIN Players ON SpecGrpUNION.Player = Players.Player_ID WHERE Players.Captain=True)) AND ((Roster_Temp_04092025.Player_Attend)=True) AND ((Players.Captain)=True));

Returns 22 records - all captains not in special group and Player_Attend true.

[MikeCt]

Sorry June7
I was given a lot to look at, your reply was the hardest for me to understand. When I did as you said I was getting results far too many results and there was nothing in the design view to alter. Something I'm not used to seeing, after playing with it for a while I figured out adding the tables manually in the design view which gave me the availability of adding Captain and attend to true which received the correct results. Thanks for working with me along the way. Now I need to study what you gave me so I can understand what you did. Thanks again

[June7]

It is a bit of hurdle understanding and managing UNION query. As I said, cannot be built in Design View, only SQL View. Simply copy/paste my example into SQL View then save the query object. Now use that query as would a table (except cannot edit data). Can pull it into query Design View like any other table or query object.

Another hurdle is dealing with nested SQL.

I built the second query by starting in Design View to build the inner query then switched to SQL View to add the outer query wrapper. Alternatively, build the inner query and save that object. Then build a third query for the nesting in the WHERE clause - in this case will require switching to SQL View and copy/paste SQL from the inner query object. Just be sure to get parentheses in there correctly. I will often build queries and save objects then copy/paste SQL statements from the objects into SQL View for nesting. Then I delete the objects no longer needed.

Here are some on-line resources for SQL:

https://www.w3schools.com/sql/

http://allenbrowne.com/subquery-01.html

http://allenbrowne.com/ser-67.html
This could be useful if the Special_Grouping table was normalized.

[madpiet]

Sounds like you're missing an Events table, and then you'd have Events--(1,M)--Roster--(M,1)--Players

Maybe role (like "Captain") is a property of Roster. (A player can be a Captain for a given Event).

As I said before, if you don't understand data modeling and normalization, this is going to be tough row to hoe. You absolutely must understand it in order to make sure your design is correct.