Help! Query design

[dmlaz68]

I am working in Access 2010 and have a table with over 50k+ rows of data. The table consists of 3 columns (GROUP[double], PLAYERS[text], VALUE [text]) and looks like this:

GROUP Players value 30 46-931 30 46-912 30 46-931 136 30 46-912 136 30 46-931 1510CC 30 46-912 1510CC 30 46-931 2045US 30 46-912 2045US 30 46-931 228 30 46-912 228 30 46-931 3500BE 30 46-912 3500BE 30 46-931 356 30 46-912 356 30 46-931 404 30 46-912 408 30 46-931 500 30 46-912 500 30 46-931 600 30 46-912 612 35 46-921 35 46-911 35 46-921 136 35 46-911 136 35 46-921 1510CC 35 46-911 1510CC 35 46-921 2045US 35 46-911 2045US 35 46-921 228 35 46-911 228

What I am attempting to do is return ONLY the groups where each player in the group holds identical values. In this example group 30 players differ in values (600 & 612 and 404 & 408) so they should be excluded from my results. Whereas GROUP 35 players all have identical values and should return true.



How do I write this query?

thanks!

[June7]

Filtering records based on values in other records of same table is not easy. Usually involves nested subquery or domain aggregate function.

I don't understand. I see duplicate values for group 30. You want only those records that are duplicate pairs in the Value field? So the output would be:

GROUP	Players		value
30	46-931
30	46-912
30	46-931		136
30	46-912		136
30	46-931		1510CC
30	46-912		1510CC
30	46-931		2045US
30	46-912		2045US
30	46-931		228
30	46-912		228
30	46-931		3500BE
30	46-912		3500BE
30	46-931		356
30	46-912		356
30	46-931		500
30	46-912		500
35	46-921
35	46-911
35	46-921		136
35	46-911		136
35	46-921		1510CC
35	46-911		1510CC
35	46-921		2045US
35	46-911		2045US
35	46-921		228
35	46-911		228

[dmlaz68]

I only want to return the GROUP# where each player in that group shares identical values. I would expect to ONLY see GROUP 35 in my results in this scenario because each PLAYER in GROUP 35 shares identical VALUES, where GROUP 30 does not.

I can't really explain it any further, and I can't wrap my head around how to accomplish this.

Thanks for helping out!

[dmlaz68]

My expected results in this scenario should look like this:
GROUP
35

[June7]

SELECT DISTINCT [Group] FROM Table1 WHERE [Group] NOT IN (SELECT [Group] FROM (SELECT Table1.Group, Nz([Value],0) AS V, Count(Nz([Value],0)) AS CV
FROM Table1
GROUP BY Table1.Group, Nz([Value],0)
HAVING Count(Nz([Value],0))=1) AS T);

Value and group are reserved words. I typed this statement in SQL View and had difficulty until I used []. Should not use reserved words as names.

[dmlaz68]

I will give it a try and let you know of the outcome.

Thank you ever so much!!