Multiple Criteria and return only one match

D

duketter

Office 2003 - I am trying to run a select query that pulls the user name if
they have access to 2 specific data items. I have tried using the "AND"
command and I get no results. I then used the "in" command and get my
matches. Here is what I have: In ("P0801ORG","P0801EMP")

However, now it is pulling dupblicate user names for P0801ORG and P0801EMP.
It is showing me Jon has access to P0801ORG and Jon has access to P0801EMP.
I just want it to pull the name of the user who has access to both of these
and just pull their name once and show it to me. I just want the query to
give me a listing of the each name that has access to both P0801ORG and
P0801EMP.

Thanks!
 
J

John Spencer

If there are no duplicate combinations (of username and the other field) you
could use

SELECT UserName
FROM YourTable
WHERE SomeField In ("P0801ORG","P0801EMP")
GROUP BY UserName
HAVING Count(*) = 2

If there is the possibility of duplicate combinations then you can do the
following

SELECT Distinct UserName
FROM Table
WHERE UserName IN (SELECT UserName from Table where SomeField = "P0801ORG")
and
UserName In (SELECT UserName from Table where SomeField = "P0801EMP")

or you can build a distinct query on the combinations and then use that as
the source for the first query replacing YourTable with a reference to the
distinct query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

Michel Walsh

Group by user and add an HAVING COUNT(*) = 2:


SELECT user
FROM somewhere
WHERE access IN ( "ORG", "EMP")
GROUP BY user
HAVING COUNT(*) = 2



THAT assumes that there is no DUPlicated pair (user, access) in the
original table.


For a variable amount of 'skills', if you have not necessary two, see
http://www.mvps.org/access/queries/qry0016.htm


Hoping it may help,
Vanderghast, Access MVP
 
Top