Removing any entrys with only one entry ( No matches )

[MikeCt]

I have a query that list records, I need to eliminate any entries that are only listed once

Id Name Team
1 Bob 1
2 Bill 1
3 John 2


4 Ted 2
5 Mike 2
6 Jim 3 * eliminate this entry from query results
8 Jack 4
9 Tim 4
10 Rick 4

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Specials.Team, Specials.DateStart, Specials.DateEnd, Specials.Join_Seperate, Roster_1.Player_Attend
FROM (Players INNER JOIN Specials ON Players.Player_ID = Specials.Player) INNER JOIN Roster_1 ON Specials.Player = Roster_1.Player_ID
WHERE (((Specials.Join_Seperate)=True) AND ((Roster_1.Player_Attend)=True) AND ((#4/9/2025#)>=[DateStart] And (#4/9/2025#)<=[DateEnd]))
ORDER BY Specials.Team;

In my attached Database, running Query1 The entry Player_ID: 47 LastName: Ferry FirstName: Frank Team entry: 3 Needs to be removed from the results (3 has no match)


Any help to point me in the right direction would be appreciated
Mike

[Welshgasman]

Where Count(Team) > 1

[June7]

SELECT * FROM Query1 WHERE Team IN(SELECT Team FROM Query1 GROUP BY Team HAVING Count(*) > 1);

or

SELECT Query1.*
FROM Query1 INNER JOIN (SELECT Team FROM Query1 GROUP BY Team HAVING Count(*) > 1) AS Query2
ON Query1.Team = Query2.Team;

The first is an editable dataset, the second is not.

[MikeCt]

That's what I tried earlier
I entered per your instructions

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Specials.Team, Specials.DateStart, Specials.DateEnd, Specials.Join_Seperate, Roster_1.Player_Attend
FROM (Players INNER JOIN Specials ON Players.Player_ID = Specials.Player) INNER JOIN Roster_1 ON Specials.Player = Roster_1.Player_ID
WHERE Join_Seperate=True AND Count(Team) >1 AND Player_Attend=True AND #4/9/2025#>=DateStart And #4/9/2025#<=DateEnd
ORDER BY Specials.Team;

Cannot have aggregate function in WHERE clause <clause>. (Error 3096)

Thanks for the quick reply

[June7]

Did you see post 3?

[jojowhite]

you can also Copy and paste this in SQL View of query designer:

SELECT Query1.*
FROM Query1
WHERE (((DCount("1","Query1","Team=" & [Team]))>1));

then you can compare the result from Query1 and this query.

[madpiet]

HAVING (COUNT(Team)) > 1

HAVING filters aggregations. WHERE doesn't.

[MikeCt]

I tried the changing of "Where Count(Team) > 1" to "HAVING (COUNT(Team)) > 1" with no success. I'm not knowledgeable enough to know if I placed the lines in the correct place, #4 shows my interpretation.

I used both #3 (editable dataset version) and #6 with excellent results. I've listed both select statements for each version

SELECT Players.Player_ID, Players.LastName, Players.FirstName, Specials.Team, Specials.DateStart, Specials.DateEnd, Specials.Join_Seperate, Roster_1.Player_Attend
FROM (Players INNER JOIN Specials ON Players.Player_ID = Specials.Player) INNER JOIN Roster_1 ON Specials.Player = Roster_1.Player_ID
Where Join_Seperate=True AND Player_Attend=True AND #4/9/2025#>=DateStart And #4/9/2025#<=DateEnd
AND DCount("1","Query1","Team=" & Team)>1
ORDER BY Specials.Team;


SELECT Players.Player_ID, Players.LastName, Players.FirstName, Specials.Team, Specials.DateStart, Specials.DateEnd, Specials.Join_Seperate, Roster_1.Player_Attend
FROM (Players INNER JOIN Specials ON Players.Player_ID = Specials.Player_ID) INNER JOIN Roster_1 ON Specials.Player_ID = Roster_1.Player_ID
WHERE Specials.Join_Seperate=True AND Roster_1.Player_Attend=True AND #4/9/2025#>= Specials.DateStart AND #4/9/2025#<= Specials.DateEnd
AND Team IN(SELECT Team FROM Query1 GROUP BY Team HAVING Count(*) > 1)
ORDER BY Specials.Team;

Thanks to all for all the help
Mike

[madpiet]

That's what HAVING is for.

Do you mean "show me all the players from the Players table that appear only once in the Roster table"? 90% of getting an answer is asking an accurate question.

[MikeCt]

Trying to be clear as to what I'm trying to achieve, I'm learning is almost as hard as writing the Select statement. The pdf I included showed what I wanted to be eliminated from my results of the Query. Even with all this excellent help I've been getting from everyone, I'm still fighting to make it all work within my program. The 2 Select statements that I showed in my last (believed to be final post) worked great in the access program. but when I moved the Select statement into my vb.net program I'm writing, the Query keeps asking where Query1 is. I have Query1 included within the access database that I'm using, which is referenced within the Vb.net program. Even if I got this to
work within my program, every event has a different roster. The Query1 would need to revise with every event. I do not know how to run a Query within the vb.net environment that another query could use later.
This only is why I was very interested in your example. I'm not sure but it seemed that it created a list with the needed results without reference to another query. Getting back to your statement about asking the question accurately is a very true statement. I sure this explanation is not very understandable, and my lack of knowledge, limits me to even know where to asks for help. If your solution works without reference to another Query I would be very interested in learning where I went wrong. Thanks once again

[madpiet]

Not gonna lie. I've been reading and responding to newsgroup posts for a long time. And what I've learned over time is that there are more effective and less effective ways to ask questions. (Super helpful, right?)

The trick is to explain clearly "you are here"... by that, I mean "These are the tables/data I have <show structure>", and this is the result I'm trying to get. The part that took getting used to is to figure out if the people you're asking can understand your question and if they have been given enough information to answer.

In a way, it's one thing I like about SQL Server that I'm not super crazy about in Access: the ability to script out table design. ( CREATE TABLE scripts ). Makes reproducing "You are here" a whole lot easier.

The super fun part is that there's a fine line between just enough information and too much. =) To be honest, one way to understand better is to read other people's questions and ask yourself "Is there enough information here to answer the question? if not, what's missing?" Once you try answering those, it helps you understand how to ask better. (Try two or three. Seriously, having the shoe on the other foot is enlightening!)

[June7]

A roster table for each event - why? Are these tables different in structure?

I know nothing about executing queries from VB.net - time to start learning?

Isn't the SQL statement passed to Access where the SQL is actually executed and dataset returned to VB.net?

Maybe embed Query1 SQL - means including its SQL statement twice:

SELECT *
FROM (Query1 SQL statement here) AS Q2 
WHERE Team IN(SELECT Team 
              FROM (Query1 SQL statement here) AS Q1 
              GROUP BY Team
              HAVING Count(*) > 1);

Remove ORDER BY clause from Query1 and be sure to exclude semi-colon.