Exclude rows with single entry in column (Part 2)

[MikeCt]

I finally found the time to rework my project. I rewrote the program to use a table I believe is correct. I've been able to retrieve the results I've need except for the final step. The query QuerySpecial1 (Attached) retrieves the result's needed but now all I need to do is eliminate any rows that do not have a matching entry in the SG_Team column.
The query QuerySpecial2 is my last effort that runs but doesn't show the correct results.

The query QuerySpecial2 results should only list

26 Critelli Jesse with SG_Team (1)
11 Bonacci Dominic with SG_Team (1)

39 DeMunda Frank with SG_Team (8)


38 Demoura Antonia with SG_Team (8)

I hope I'm doing this properly now, and I know the question was probably answered already, but I'm not seeing it yet
Once again thanks for any help
Mike

[June7]

Change WHERE to:

WHERE SG_Team IN (SELECT [SG_Team] FROM [QuerySpecial1] GROUP BY [SG_Team] HAVING COUNT(*) > 1);

QuerySpecial1 could be:

SELECT Specials.SG_Team
FROM (Specials INNER JOIN Players ON Specials.SG_Player = Players.PL_ID) INNER JOIN Roster_Temp_04162025 ON Players.PL_ID = Roster_Temp_04162025.RT_PlayerID
WHERE (((Roster_Temp_04162025.RT_Attend)=True) AND ((Players.PL_Active)=True) AND ((Specials.SG_Join)=True) AND ((#4/16/2025#)>=[SG_DateStart] And (#4/16/2025#)<=[SG_DateEnd]))
GROUP BY Specials.SG_Team
HAVING (((Count(Players.PL_ID))>1));

Then QuerySpecial2:

SELECT Players.*, Specials.*, Roster_Temp_04162025.RT_Attend
FROM (Specials INNER JOIN Players ON Specials.SG_Player = Players.PL_ID) INNER JOIN Roster_Temp_04162025 ON Players.PL_ID = Roster_Temp_04162025.RT_PlayerID
WHERE (((Specials.[SG_Team]) In (SELECT [SG_Team] FROM [QuerySpecial1])));

Or QuerySpecial2:

SELECT Players.*, Specials.*, Roster_Temp_04162025.RT_Attend
FROM QuerySpecial1 INNER JOIN ((Specials INNER JOIN Players ON Specials.SG_Player = Players.PL_ID) INNER JOIN Roster_Temp_04162025 ON Players.PL_ID = Roster_Temp_04162025.RT_PlayerID) ON QuerySpecial1.SG_Team = Specials.SG_Team;

[jojowhite]

you can try this query:

SELECT QuerySpecial1.*
FROM QuerySpecial1 
WHERE DCOUNT("1", "QUERYSPECIAL1", "SG_TEAM = " & [SG_TEAM]) > 1

[NoellaG]

Using domain functions in a query is a bad practice: for every result row the engine has to make a table scan. So a result of 50 rows with a table counting 1000 rows mean 50000 rows to scan.
Using a calculation on the left side of an equation in a where claus is a very bad practice, no indexes can be used.
using
WHERE DCOUNT("1", "QUERYSPECIAL1", "SG_TEAM = " & [SG_TEAM]) > 1

combines 2 very bad practices, so I would go with the solution offered by June

[jojowhite]

how about this wise-guy:

SELECT A.* FROM QuerySpecial1 AS A INNER JOIN (SELECT QuerySpecial1.SG_Team
FROM QuerySpecial1
GROUP BY QuerySpecial1.SG_Team
HAVING (((Count("1"))>1))) AS B ON A.SG_TEAM = B.SG_TEAM

[MikeCt]

The attached query's I sent had QuerySpecial1 listing 6 rows of records

Row PL_Id PL_LName SG_Team
1 26 Criteli 1
2 11 Bonacci 1
3 7 Baker 3 Eliminate
4 155 Tomas 4 Eliminate
5 39 DeMunda 8
6 38 Demoura 8

The second query QuerySpecial2 was my failed attempt at eliminating rows 3 & 4 from the results

The query I was asked to rework my QuerySpecial1 into, gave the results

SG_Team
1
8
Which I believe, I could use to pull teams 1 & 8 from my special QuerySpecial1 since they pass the test of having more than 1 entry
The reworks of QuerySpecial2 I really do not understand what they are offering

I'm trying to have one query retrieve the data needed, without the need of a query resting within the database. If the query resting in the database, references a specific table in the database. I cannot change that reference. I'm working in the VB.net environment and my SQL statements are hardcoded in the program, which gives me the flexibility to change dates and references along the way. Which brings me back to, I can use your rework of my QuerySpecial1 to pull the results needed, but I was hoping for a much cleaner process.

I'm using this program to learn more of what SQL can do to eliminate the coding of these types of processes and also learn to do better database structure. The journey has been very successful but it's harder than I thought it would be.

I'm not sure if what I'm asking is clear, or if it's even possible to get the results using SQL without have a query resting in the within the database, but this what I'm trying to learn

Thanks all
Mike

[jojowhite]

if you are not saving the query and you will hardcode it, try this:

SELECT A.* FROM (SELECT Players.PL_ID, PL_Active, PL_Lname, PL_Fname, PL_Captain, PL_GoldPlayer, PL_Handicap, SG_Join, SG_Team, SG_DateStart, SG_DateEnd, Roster_Temp_04162025.RT_Attend
FROM (Specials INNER JOIN Players ON Specials.SG_Player = Players.PL_ID) INNER JOIN Roster_Temp_04162025 ON Players.PL_ID = Roster_Temp_04162025.RT_PlayerID
WHERE PL_Active=True AND SG_Join=True AND Roster_Temp_04162025.RT_Attend=True And  #4/16/2025# >= SG_DateStart  And  #4/16/2025# <= SG_DateEnd) AS A  
INNER JOIN (SELECT  SG_Team 
FROM (Specials INNER JOIN Players ON Specials.SG_Player = Players.PL_ID) INNER JOIN Roster_Temp_04162025 ON Players.PL_ID = Roster_Temp_04162025.RT_PlayerID
WHERE PL_Active=True AND SG_Join=True AND Roster_Temp_04162025.RT_Attend=True And  #4/16/2025# >= SG_DateStart  And  #4/16/2025# <= SG_DateEnd GROUP BY SG_TEAM HAVING COUNT(SG_TEAM)>1) AS B 
ON A.SG_TEAM = B.SG_TEAM

See Query3.

[MikeCt]

The results are exactly what I need. Now I have to figure out how it works.
I can't thank you enough for your time, looking at it I realized I wasn't very close to an answer on my own. It seemed so simply to take the results from QuerySpecial1 and produce the results needed. I thought there might have been something like
"Where Not Distinct(SG_team)"

Looking forward to using this SQL statement in the program to see how it holds up with the different weekly events. I'm still rewriting the program to stop using the Special_Groupings table you helped me with previously. I wish I had just started rewriting the program much earlier, but I would have learned nearly as much. Failing is a great way of really learning why the little thing matter.

Thanks again
Mike