Query help please

[Kelly 1st]

Hello,

If someone could help me... i'm not very good at queries...

I have a table with say 10 different columns, but i'm interested in just 2...

Column 1 is a "policy number" which can have entries that are the same
Column 2 is a "post code/zip code" which can also have entries that are the
same

What i would like to do is pull out policy numbers where entry 1 of a policy
number has a different postcode than entry 2

E.g

Policy no. Post code
A1 C02 8BU
A1 C02 8BU
A2 CM4 5AL
A3 SW1 5PB
A3 SW1 5PB
A3 E17 7KH
A4 ZE1 0JH
A4 M2 2ED


I wouldn't want:
A1 as the postcode is the same on both entries... or
A2 as it is by itself
I WOULD want:
A3 as the post code differs on the thrird entry... and
A4 as the post code differs on the second entry

the OUTPUT i would be looking for in the query could just be the policy number

Many Thanks

 

[Andy Hull]

Hi Kelly

Go into the SQL view of the query and enter...

select [PolicyNumber]
from [TableName]
group by [PolicyNumber]
having min(PostCode) <> max(PostCode)


Obviously, use your own table and column names

Hope this helps

Andy Hull

 

[John Spencer]

And if you want to show all the records involved and want an editable query
then you can use Andy Hull's suggestion as a subquery in a where clause.

SELECT *
FROM [TableName]
WHERE [PolicyNumber] IN
(select [PolicyNumber]
from [TableName]
group by [PolicyNumber]
having min([PostCode]) <> max([PostCode]) )

Post back if you need help building the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Kelly

Go into the SQL view of the query and enter...

select [PolicyNumber]
from [TableName]
group by [PolicyNumber]
having min(PostCode) <> max(PostCode)


Obviously, use your own table and column names

Hope this helps

Andy Hull

Hello,

If someone could help me... i'm not very good at queries...

I have a table with say 10 different columns, but i'm interested in just
2...

Column 1 is a "policy number" which can have entries that are the same
Column 2 is a "post code/zip code" which can also have entries that are
the
same

What i would like to do is pull out policy numbers where entry 1 of a
policy
number has a different postcode than entry 2

E.g

Policy no. Post code
A1 C02 8BU
A1 C02 8BU
A2 CM4 5AL
A3 SW1 5PB
A3 SW1 5PB
A3 E17 7KH
A4 ZE1 0JH
A4 M2 2ED


I wouldn't want:
A1 as the postcode is the same on both entries... or
A2 as it is by itself
I WOULD want:
A3 as the post code differs on the thrird entry... and
A4 as the post code differs on the second entry

the OUTPUT i would be looking for in the query could just be the policy
number

Many Thanks

 

[Kelly 1st]

Thank you to you both!
You've been very helpful