Link to home
Create AccountLog in
Avatar of Jonathan Mori
Jonathan MoriFlag for United States of America

asked on

Access SQL Query Question

I have a large table that has a Column of numbers ranging from 0001 - 0008 and they represent a frequency of how often a payment is due for a  property owner, such as Annually, Semiannually, etc.

I  need a query that filters it out this way. It should only display the 0001 if its all the property has, but if it has more than just 0001 then it has to display all the records except for the one that has 0001 frequency code.

Property    Frequency
__________________
Property1  0002
property1  0004
property1  0005
property1  0001

property2 0001

property3 0001
property3 0002
property3 0004
property3 0008

property4 0001
property4 0002
property4 0003

property5 0001

It's a little confusing I know, that's why i'm having trouble with it!
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
HI

try this
select Property, Frequency from T 
where Property != "0001"  
 
union
 
select Property, Frequency from T 
where Property = "0001"  and
Property not in (select Property, Frequency from T 
where Property != "0001")

Open in new window

Avatar of Jonathan Mori

ASKER

Thanks, I'll try them out, I'm sorry but i have to take off soon so If I can't get them workin today I'll get back to you tommorrow! Thanks a lot.
Avatar of Sean Stuber
Sean Stuber

elimesika,  
your query doesn't eliminate 0001 for properties that have more than one frequency.
Yes it does ! , check it ....

In the first statement I am taking all  values except 0001

in the second I am taking 0001 only if it has no matching with the first statement
I ran it, it didn't work  I got 13 rows returned,  it should be only 10.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
THANK YOU!!
glad I could help