Getting multiple values from a single column

[TheCandyman]

I'm trying to write a SQL statement for Access that will look at one column and only show those that have both values. For example, in looking for users that have both 'aaaa' AND 'bbbb' would be User1.

Column1  Column2
User1     aaaa
User2     bbbb
User1     bbbb
User2     cccc
User3     aaaa

Currently I'm getting all those that have either 'aaaa' or 'bbbb' but not both, and simply switching the OR to a AND results in no results.

SELECT DISTINCT Column1
FROM table
WHERE (Column2='aaaa' OR Column2='bbbb');

I asked in the SQL form but only got commands that run on SQL Server and not MS Access. Any ideas? I'm thinking a nested SQL query but I'm not sure where to start.

 

[SkipVought]

Hi,

SELECT DISTINCT Column1
FROM table A, table B
WHERE A.Column1=B.Column1
  And (A.Column2='aaaa' AND B.Column2='bbbb' OR 
       A.Column2='bbbb' AND B.Column2='aaaa')

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops

Select Distinct A.Column1
.....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

SELECT Column1
FROM table
WHERE Column2 In ('aaaa','bbbb')
GROUP BY Column1
HAVING COUNT(*)=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Another possible solution is:

SELECT Column1 FROM (SELECT Column1, Column2
FROM tblCandyMan
WHERE Column2 In ('aaaa','bbbb')
GROUP BY Column1, Column2)
GROUP BY Column1
HAVING COunt(*) = 2

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

SELECT a.col1
FROM table a inner join table b on a.col1 = b.col1
where a.col2 = 'aaaa' and b.col2='bbbb'


works also, doesn't it?

 

[TheCandyman]

SkipVought - your suggestion ran for about 10 minutes. there are only 2 columns and 11,000 records so it's not huge. It eventually gave me all those with either 'aaaa' or 'bbbb' and those with both 'aaaa' and 'bbbb' so it didn't give the results needed.

PHV - i can't get that to return any results.

dhookom - your suggestion works! I tested it a few times with different values and it's correct.


Thanks everyone for the suggestions, was going cross eyed trying to figure this out by myself!!

 

[dhookom]

I tried PH's suggestion with your sample data and it worked exactly like mine. I would be concerned about having two records with the same Column1 and Column2 values. The following data might return a false result.

Column1  Column2
User1     aaaa
User1     aaaa
User1     cccc

Duane
Hook'D on Access
MS Access MVP