Query help please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
..

Andy Hull said:
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


Kelly 1st said:
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
Click to expand...
Click to expand...
 
Back
Top