Exclude rows with single entry in column

[MikeCt]

I have this Select statement in my query that produces the results shown in the attached pdf
I need to exclude any row, that has a single entry in the SpecGrpUnion.ID (ID) column
Player_ID LastName FirstName ID
********* ********** ********* ****
13 Bonacci Domminic 1 single entry(1) = exclude
80 Maher Mike 25 single entry(25) = exclude

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Players.Captain, Players.GoldPlayer, Players.Handicap, SpecGrpUnion.ID, Roster_Temp_04092025.ID
FROM (Players INNER JOIN SpecGrpUnion ON Players.Player_ID = SpecGrpUnion.Player) INNER JOIN Roster_Temp_04092025 ON Players.Player_ID = Roster_Temp_04092025.Player_ID
WHERE Join_Seperate = True And Player_Attend = True And #4/9/2025# >= DateStart And #4/9/2025# <= DateEnd


ORDER BY SpecGrpUnion.ID, Players.Captain, Players.Handicap;

See attached database
Any help will be appreciated, still learning
Thanks Mike

[Welshgasman]

Create an aggregate query where CountSpecGrpUnion.ID) > 1 and join on that.

[MikeCt]

I've tried reading up on aggregate query and tried to different approaches with the same error message
Blue reflects my changes

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Players.Captain, Players.GoldPlayer, Players.Handicap, COUNT(SpecGrpUnion.ID)>1, Roster_Temp_04092025.ID
FROM (Players INNER JOIN SpecGrpUnion ON Players.Player_ID = SpecGrpUnion.Player) INNER JOIN Roster_Temp_04092025 ON Players.Player_ID = Roster_Temp_04092025.Player_ID
WHERE Join_Seperate = True And Player_Attend = True And #4/9/2025# >= DateStart And #4/9/2025# <= DateEnd
ORDER BY SpecGrpUnion.ID, Players.Captain, Players.Handicap;

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Players.Captain, Players.GoldPlayer, Players.Handicap, SpecGrpUnion.ID, Roster_Temp_04092025.ID
FROM (Players INNER JOIN SpecGrpUnion ON Players.Player_ID = SpecGrpUnion.Player) INNER JOIN Roster_Temp_04092025 ON Players.Player_ID = Roster_Temp_04092025.Player_ID
WHERE Join_Seperate = True And Player_Attend = True And #4/9/2025# >= DateStart And #4/9/2025# <= DateEnd
GROUP BY SpecGrpUnion.ID HAVING COUNT(SpecGrpUnion.ID)>1
ORDER BY SpecGrpUnion.ID, Players.Captain, Players.Handicap;

Error message
Your query does not include the specified expression 'Player_ID' as part of an aggregate function

Thanks for the help
Mike

[madpiet]

Do you mean Exclude Players from Players table that have a single entry in the Special Groupings table? The first step to getting an answer is to ask a carefully worded question.
Oh, and unpivot that Special Groupings table if you want to filter it. It should be (GroupID, PlayerID, StartDate, EndDate)... Yep, a little normalization is a beautiful thing. It makes queries SO much easier!

[Welshgasman]

You cannot have aggregate function in a simple query. I already tried that and I could not get it to work.
So go simple. Create a query that omits single IDs, then use that in place of your Grp query/table.

Why the alias?, you are only using it once?

[madpiet]

okay. Gotcha...

the first thing to do is find all the players that have only one entry in said table. Use a totals query... something like this:

SELECT Player_ID
FROM Players p
WHERE p.Player_ID IN (SELECT SpecGrpUnion.Player
FROM SpecGrpUnion
GROUP BY SpecGrpUnion.Player
HAVING COUNT(SpecGrpUnion.ID) = 1);

Then remove those Player_IDs from (say) the Players table:

SELECT *
FROM Players p
WHERE p.Player_id NOT IN (
SELECT Player_ID
FROM Players p
WHERE p.Player_ID IN (SELECT SpecGrpUnion.Player
FROM SpecGrpUnion
GROUP BY SpecGrpUnion.Player
HAVING COUNT(SpecGrpUnion.ID) = 1)
);

Sorry 'bout that! Does that work for you?

[MikeCt]

This is a read only query, nothings need to be removed from the tables. Not sure what is meant by unpivot the table. The layout of the table is what I was given back with my first post to this forum. I know the Special_Groupings table is not correct, but it's so intwined into the program that it would cause a major rewrite. As for your suggestion in the last post. I tried to implement this code and got the same error message. I'm sure I didn't do justice to the suggestion. Failing is a great way to learn

Error message
Your query does not include the specified expression 'Player_ID' as part of an aggregate function

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Players.Captain, Players.GoldPlayer, Players.Handicap, SpecGrpUnion.ID, Roster_Temp_04092025.ID
FROM (Players INNER JOIN SpecGrpUnion ON Players.Player_ID = SpecGrpUnion.Player) INNER JOIN Roster_Temp_04092025 ON Players.Player_ID = Roster_Temp_04092025.Player_ID
WHERE Join_Seperate = True And Player_Attend = True And #4/9/2025# >= DateStart And #4/9/2025# <= DateEnd
FROM SpecGrpUnion
GROUP BY SpecGrpUnion.Player
HAVING COUNT(SpecGrpUnion.ID) - 1
ORDER BY SpecGrpUnion.ID, Players.Captain, Players.Handicap;


I did attach the database and pdf with my first post to help show what I'm looking for (your question was very valid)
Thanks again Mike

[Welshgasman]

Having ID in most tables is not helping.
Give each field a decent name.

Do you want the count taking into account the date filter or not?
As player 13 has more than 1, 80 just the one, so that does not make a difference.

Probably not helping as the db is not normalized with that SpecialGroupings table.

[MikeCt]

I'm just trying to eliminate all row from the query results that doesn't have a shared SpecGrpUnion.ID column number. In the pdf I included in post one, the numbers 1 & 25 appear only once in the SpecGrpUnion.ID column. If any number is distinct in the SpecGrpUnion.ID column I would like to have it not show up in the query results.

Thanks

[June7]

Not sure what is meant by unpivot the table. The layout of the table is what I was given back with my first post to this forum.

Nothing in that first thread advised to use that table structure - you gave it to us. I did offer a method to deal with it (UNION query - which is "unpivoting" non-normalized structure to normalized).

Wasn't this question already asked and solved in second thread? https://www.accessforums.net/showthread.php?t=90567

Why do players 21 and 135 not have records in Roster_Temp?

A GROUP BY must include every field that is in the SELECT unless it is an expression.

So consider this:

SELECT Special_Groupings_Query.* FROM Special_Groupings_Query WHERE SpecGrpUnion.ID IN
(SELECT [SpecGrpUnion].[ID]
FROM Special_Groupings_Query
GROUP BY Special_Groupings_Query.[SpecGrpUnion].[ID]
HAVING Count(*) >1);

or

SELECT Special_Groupings_Query.* FROM Special_Groupings_Query INNER JOIN (SELECT [SpecGrpUnion].[ID]
FROM Special_Groupings_Query
GROUP BY Special_Groupings_Query.[SpecGrpUnion].[ID]
HAVING Count(*) >1) AS Q1
ON Special_Groupings_Query.SpecGrpUnion.ID=Q1.ID;

[MikeCt]

(1) I did not mean to throw blame; the union query you offered dealt with the problem I needed solving, and I'm very appreciative for it. I started the project to learn, and that is what I've been doing. I started this project long before I joined this forum, and my earlier decisions have made problems for me. But these problems have led me to learning much more than expected, so all is good.
(2) I don't believe this question, was what I asked in the second post. Maybe the answer to this question is imbedded in the that answer, but I'm still learning so I'm not seeing it
(3) Records 21 and 35 are missing from the roster because those players are not active (See players table, active column) just part of my debugging process
(4) I'm going to try to utilize what you sent me, to see if I can get the results needed. Your examples don't have a WHERE statement so I'm not sure where to place the conditions listed below.

WHERE Join_Seperate = True And Player_Attend = True And #4/9/2025# >= DateStart And #4/9/2025# <= DateEnd

Nothing is easy but it is very interesting
Thanks for your response
Mike

[June7]

That criteria is already in your existing query. That query is used in my suggested SQL.

[MikeCt]

Ok I used the example

SELECT Special_Groupings_Query.* FROM Special_Groupings_Query WHERE SpecGrpUnion.ID IN
(SELECT [SpecGrpUnion].[ID]
FROM Special_Groupings_Query
GROUP BY Special_Groupings_Query.[SpecGrpUnion].[ID]
HAVING Count(*) >1);

I got the exact results I needed. From what I see your using the SpecGrpUnion query to pull the muti columns into one column. Then the Special_Grouping_Query to get all the listing per the different where statements.
Then finally using the select statement above to exclude the distinct SpecGrpUnion.ID
I didn't know you could nest these statements together like this. I have so much to learn. One last question, from what I've seen in all the examples I've looked these past days, is it possible to put these three Query's together in a statement
so they run one after the other. I've seen SQL select strings with multiple SELECT in them so maybe this is what I've been seeing. The only reason I ask is I'm working with Vb.net using access in the background for my database. I've stored the SpecGrpUnion query
in with the tables. If I can place these 3 statements together in my code, I don't have to worry about a query getting deleted breaking the program.


Thanks again, this has been very post
Mike

[June7]

Access does not allow sequential processing of queries in one statement. If you want that, will have to migrate data to SQLServer or MySQL or Oracle or other where you can build views or stored procedures with CTE (common table expressions).

Nesting the SQL statements of UNION and Groupings queries would be far too complicated.

Don't delete necessary queries.

[madpiet]

I think this would be infinitely easier to deal with using SQL Server and Common Table Expressions. But that's likely not a good move for you right now for a number of reasons. Maybe the simplest option is to write one query at a time and give it a sensible name (and maybe replace the spaces in the name with underscores)... then you can just use the queries you already have and join them in a new query.