I hope I can word this so that you can understand. I am using Access
2007 and I have a simple query that returns the following results:


User_ID SAP Role
smith abc:je entry

But within the results of this query there are roles that are common
to many users. I want to find the same roles that are assigned to
users and then to find the roles that are different. Does this make
sense? This way I can show all roles that users have that are the same
and then conversely show which roles some people have and others
don't.

I appreciate the assistance.

Mark

mpdsal wrote:
results you want from that sample data - a picture is worth a thousand
words. Additionally, going through this exercise might allow you to figure
out how to do it on your own.

On Jan 11, 8:42*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:

Bob,

Good point. Here is what the data looks like: ColA is User ID and ColB
is RoleName. What I would like to do is show the results of all roles
that are common to users, in this case MarkD, BillS, and JoeD have
Role2 assigned. This way I can prepare a report that says these users
all have the same role(s) assigned to them. Does this make it a bit
more clearer?


UserID RoleName

MarkD Role1
MarkD Role2
MarkD Role3
BillS Role1
BillS Role2
BillS Role26X
JoeD Role2
JoeD Role3
JoeD Role42B

So my new query showing common roles to each user would look like
this:
MarkD Role2
BillS Role2
JoeD Role2

Thanks

Mark

mpdsal wrote:
Common to _all_ users is more correct isn't it? If BillS had Role3 in
addition to the others the results should be

MarkD Role2
BillS Role2
JoeD Role2
MarkD Role3
BillS Role3
JoeD Role3

Correct?
One way would be to compare the count of users for each role to the count of
distinct users in the database. The cleanest way to explain and do this is
by using saved queries:

Saved query 1 - DistinctUsersCount:
select count(*) as CountDistinct from (select distinct userid from
tablename) as q

Saved query 2 - CommonRoles:
select rolename,count(*) from tablename
group by rolename
having count(*) = (select CountDistinct FROM DistinctUsersCount)

Final query:
select userid,rolename from tablename as t
where exists (select * from CommonRoles as c
where = c.rolename = t.rolename)

Bob Barrows wrote:
Oops, an extra "=" in there - how'd that happen??
where c.rolename = t.rolename)