Multiple Criteria and return only one match

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
..
 
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
 
Back
Top