how to select those data?

[xq]

i'm trying to select distinct number from a table, i think sql can do that, but don't know how,

TableA:
No. Status
1 Y
1 Y
1 Y
2 N
2 N
3 Y
3 N
3 Y

i want to get distinct number which have all 'Y' status, the result should be 1, No. 3 should not be selected, anybody could help me to have a look my code, how to change it to get the result i want? thanks million!

SELECT DISTINCT(No)
FROM TABLEA
WHERE Status = 'Y'

 

[JoanneM]

try this and see how you go

SELECT DISTINCT Tabla.no, Tablea.status
FROM Tablea
WHERE (((Tablea.status)=-1))
WITH OWNERACCESS OPTION;
regards

jo

 

[xq]

thanks for ur reply, but what is 'WITH OWNERACCESS OPTION;'?
i had syntax error on it.

 

[JoanneM]

sorry so used to seeing I didn't notice it.
'WITH OWNERACCESS OPTION;'?


this is a little gem when it comes to security in access

you can set it to be default on all queries created and it means that your queries will run if you boob a little on security ( nobody is perfect)
You can just delete it and your query will work fine

jo

 

[xq]

thanks, but it still picks up No 3. isnt' really working.

 

[WayneRyan]

xq,

I haven't tried this but ...

SELECT DISTINCT(No)
FROM TABLEA
WHERE Status = 'Y' and No Not In
(SELECT DISTINCT(No)
FROM TABLEA
WHERE Status = 'N');

HTH,
Wayne

 

[xq]

thanks million, it works!