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!

SQL

Last Comment

Sean Stuber

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Sean Stuber

7/17/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

elimesika

7/17/2008

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

Jonathan Mori

7/17/2008

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.

Sean Stuber

7/17/2008

elimesika,
your query doesn't eliminate 0001 for properties that have more than one frequency.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

elimesika

7/17/2008

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

Sean Stuber

7/17/2008

I ran it, it didn't work I got 13 rows returned, it should be only 10.

SOLUTION

Sean Stuber

7/17/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION