Getting the opposite results from a query that is wanted

[MikeCt]

I have a simple Query which shows all Captains attending the event
Table(1) Players (Players information )
Table(2) RosterTemp_04092025 ( Player attending )


[SELECT Players.Player_ID, Players.LastName, Players.FirstName
FROM Players INNER JOIN RosterTemp_04092025 ON Players.Player_ID = RosterTemp_04092025.Player_ID


WHERE (((Players.Captain)=True) AND ((RosterTemp_04092025.Player_Attend)=True));]


Results
2 Ablondi Bill
7 Badillo Ernie
24 Coughlin Dan
29 Gross Bill
35 Maxis Joe
42 Durkin Ed
48 Specs Tom
53 Foss Greg
64 Jones Frank
67 Kerm John
70 Becks Tim
78 Mack Roger
80 Maher Mike
84 Mays William
86 Palmer Bob


15 Captains total
There is a Special group that has 3 captains within it (29 Gross Bill, 67 Kerm John, 80 Maher Mike)
Table(1) Players (Player information)
Table(2) RosterTemp_04092025 (Player attending)
Table(3) Special_Groupings (Special Player attending)




This SQL statement below is to create a query with the captains in the special groups removed from the total list
[SELECT Players.Player_ID, Players.LastName, Players.FirstName, Players.Handicap, Special_Groupings.DateStart, Special_Groupings.DateEnd, Special_Groupings.Join_Seperate
FROM (Players Inner JOIN RosterTemp_04092025 ON Players.Player_ID = RosterTemp_04092025.Player_ID) Inner JOIN Special_Groupings ON (RosterTemp_04092025.Player_ID = Special_Groupings.Player_1) or (RosterTemp_04092025.Player_ID =
Special_Groupings.Player_2) or (RosterTemp_04092025.Player_ID = Special_Groupings.Player_3) or (RosterTemp_04092025.Player_ID = Special_Groupings.Player_4)
GROUP BY Players.Player_ID, Players.LastName, Players.FirstName, Players.Handicap, Players.Captain, RosterTemp_04092025.Player_Attend, Special_Groupings.DateStart, Special_Groupings.DateEnd, Special_Groupings.Join_Seperate
HAVING (((Players.Captain)=True) AND ((RosterTemp_04092025.Player_Attend)=True) AND ((Special_Groupings.DateStart)=#4/9/2025#) AND ((Special_Groupings.DateEnd)=#4/9/2025#) AND ((Special_Groupings.Join_Seperate)=True))
ORDER BY Players.LastName, Players.FirstName;]


Problem is the results from my query, is a list of players that are in the special group, where the results I'm looking for is the players not in the special group
Results of the problem query are
29 Gross Bill
67 Kerm John
80 Maher Mike


Results needed
2 Ablondi Bill
7 Badillo Ernie
24 Coughlin Dan
35 Maxis Joe
42 Durkin Ed
48 Specs Tom
53 Foss Greg
64 Jones Frank
70 Becks Tim
78 Mack Roger
84 Mays William
86 Palmer Bob




Any help would be appreciated
MikeCt

[madpiet]

SELECT <field list>
FROM <MyTable>
WHERE <MyTable>.KeyColumn NOT IN ( SELECT KeyColumn FROM OtherTable WHERE OtherTable.Key = MyTable.KeyColumn )

[Welshgasman]

So include those special as criteria that you do not want, as they also appear to be in your non special group?

[MikeCt]

Fist I've been programming for years but my knowledge of Sgl is very limited.
As for your statement WHERE <MyTable>.KeyColumn NOT IN ( SELECT KeyColumn FROM OtherTable WHERE OtherTable.Key = MyTable.KeyColumn )

not sure what you're saying, there is no WHERE in my Select statement, which is a first for me. I used access Query design to help me, even with that, I found I had to make changes in the Sql view to get it to work.
It the FROM part of the statement I had to change AND to OR thru the statement to get any results. Thanks

[MikeCt]

I wish I understood better what you mean with using the criteria to do what I want. My knowledge of Sql is very limited

[June7]

I'm looking for is the players not in the special group

So did you try suggested query using NOT IN criteria?

[MikeCt]

I've reread your answer a few times, and I believe what you're asking is not possible for me. The specials groups counts and sizes will always be different every week

[madpiet]

I've reread your answer a few times, and I believe what you're asking is not possible for me. The specials groups counts and sizes will always be different every week

This is a bit of a brutal response, but knowing how to write queries - even with the query UI - is fundamental to using Access (or any database... if you think Access is hard, try SQL Server!)

Can you post a sample database with only the tables needed to answer the question?

[June7]

Similar field names like Player_1, Player_2, Player_3, Player_4 indicates a non-normalized data structure which is probably why you are struggling to get desired output.

I also recommend you provide db for analysis.

[Welshgasman]

I believe you need an AND and the subquery for the NOT IN.
If you are incapable of doing that, then upload your dB, or change your structure so specials are marked as such. Then ignore those marked as such.

[MikeCt]

I uploaded the database along with the Query I've been working on

[madpiet]

Okay! Now we can get somewhere!

First problem... the repeating fields bit in SpecialGroupings. Instead of adding columns, add rows.

you could have the logical equivalent with a table structured like this:

CREATE TABLE TeamGroupings(
GroupID INT NOT NULL,
PlayerID INT NOT NULL,
DateStart DATE NOT NULL,
DateEnd DATE NOT NULL
);

Not 100% on what are the minimum columns to identify uniqueness.
Oh, and having 0 as a playerID is very different than a null (no value) playerID. 0 in this context is a strange value (as IDs normally start at 1, but 0 is a ??? kind of thing. if a value is "unknown", the proper value in the column is NULL.

Once we have team groupings and individual players... Say for the sake of argument that Players is super simple, it's just maybe (PlayerID INT NOT NULL PRIMARY KEY <so unique>, FirstName TEXT(15) NOT NULL, LastName TEXT(15) NOT NULL). (PlayerID is just a unique number assigned to each player record, and FirstName and LastName are just bits of informatio about the player.

Players not on a team:
SELECT PlayerID, FirstName, LastName
FROM Players
WHERE PlayerID NOT IN (SELECT PlayerID FROM TeamGroupings);

[June7]

And if you really, really don't want to normalize SpecialGroupings, the alternative is to use a UNION query to first rearrange the fields then use that query as source for subsequent queries. There is no designer for UNION, must type or copy/paste in SQL View.

First query: SpecGrpUNION

SELECT ID, 1 AS SrcFld, Player_1 AS Player, DateStart, DateEnd, Join_Seperate FROM Special_Groupings WHERE Player_1 > 0
UNION ALL SELECT ID, 2, Player_2, DateStart, DateEnd, Join_Seperate FROM Special_Groupings WHERE Player_2 > 0
UNION ALL SELECT ID, 3, Player_3, DateStart, DateEnd, Join_Seperate FROM Special_Groupings WHERE Player_3 > 0
UNION ALL SELECT ID, 4, Player_4, DateStart, DateEnd, Join_Seperate FROM Special_Groupings WHERE Player_4 > 0;

Second query:

SELECT *
FROM Players
WHERE Player_ID NOT IN (SELECT [Player_ID] FROM SpecGrpUNION INNER JOIN Players ON SpecGrpUNION.Player = Players.Player_ID WHERE Players.Captain=True);


NOTE: Might want to correct spelling of Seperate to Separate.

[MikeCt]

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

[June7]

Exactly what is purpose of Roster_Temp table?

If your last post addresses me (June7), I tested queries (post 13) with your data and certainly does return results. If you need additional filter based on Roster_Temp Player_Attend, include that table in the second query.

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));

Query returns 35 records.



Player_ID in Roster_Temp should NOT be defined as primary key - fix that.

Are you creating a new table for each date? Conventional approach would be one table and instead of Player_Attend have DateAttended field and only enter players who attended. What are they attending? Maybe this table should actually be 2 tables - a parent table for info about whatever is attended and dependent table for who attended. Include date filter criteria in query. Advise not to use exact same field name in multiple tables.