Find commonalities in multiple rows 2

[cdgeer]

I am trying to write a query to find all the Rows with common data in these fields:[DataSource], [AccessLevel],[AccessCap]. There is another field named [EEID]. It is a text datatype and represents a unique ID for a person. What I'm trying to do is group all the [DataSource], [AccessLevel], [AccessCap] that is common for All the [EEID]s that I want to use as criteria. There could be many. Basically, the [EEID] matches a [JobTitle] so I'm trying to select all EEIDs that have the same [JobTitle] and then find what [DataSource], [AccessLevel], [AccessCap] that they all have in common. (and then maybe what they each have that is not in common.)

 

[traingamer]

To find how many rows have common data, use Group By.
Something like (not tested) :

Select [DataSource], [AccessLevel], [AccessCap], count([EEID]) as CountOfEEID 
from yourtable
Group By [DataSource], [AccessLevel], [AccessCap]

That will show you how many of each match. You may want to

Order by count([EEID]) desc

and / or limit to those where there is more than one

where count([EEID]) > 1

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[PHV]

Greg, testing the value of an agregate function is permitted only in an HAVING clause.

 

[traingamer]

Greg, testing the value of an agregate function is permitted only in an HAVING clause.

Quite right. I was typing, rather than thinking.

HAVING (Count([EEID]) > 1)

would work.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[cdgeer]

Thanks guys, I tried this earlier (similar to your's) before I read your response but it doesn't seem to work right:
SELECT y_tmpMinNecCol.DataSource, y_tmpMinNecCol.AccessLevel, y_tmpMinNecCol.EEID
FROM y_tmpMinNecCol
WHERE (((y_tmpMinNecCol.DataSource) In (SELECT [DataSource] FROM [y_tmpMinNecCol] As Tmp GROUP BY [DataSource],[AccessLevel],[EEID] HAVING Count(*)>1 And [DataSource] = [y_tmpMinNecCol].[DataSource] And [EEID] = [y_tmpMinNecCol].[EEID])));
I'm wondering about the HAVING Count(*)>1 part. What if there are 3, 4 or many more? WHat if I did a separate query to determine the number of distinct [EEID]s and then did something like:
HAVING Count(*)= CountD("*", "[DistinctEEID]") ? I know it doesn't work. Syntax error. Do you know why?