Unique Records with Duplicate Fields

[razman10]

Hello,

I apologize if this has already been answered, I could not find the answer anywhere.

I have a large database (almost 400,000 records) each with a customer's name, address etc, year & month, and sales. I have found many records with the same zip code but somehow have a different city/town name. Is there any way to isolate these so I can correct each?

Example:

County Zip Code City State
YORK 03902 CAPE NEDDICK ME
YORK 03902 YORK ME
YORK 03903 ELIOT ME
YORK 03903 ELLIOT ME
YORK 03903 KITTERY ME
YORK 04002 ALFRED ME
YORK 04002 LYMAN ME
YORK 04005 BIDDEFORD ME
YORK 04005 BIDDEFORD, ME
CUMBERLAND 04011 BIRCH ISLAND ME
CUMBERLAND 04011 BRUNSWICK ME

Thanks in advance for any help.

 

[lespaul]

SELECT DISTINCT ZipCode, City FROM TableName

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[razman10]

Thank you Leslie.

That solution provides all unique zips, regardless of non-matching cities. However I am looking for only unique zips with non-matching cities.

Can this be done? I know it can be done in Excel, however with the number of records I have, that is not feasible.

 

[lespaul]

Ok, from your example data above, which records would you want to return in the query results?

leslie

 

[razman10]

In the example above I only included duplicates. In the example I have here below, I do not want to see: 03901, 03904, 03906, 03908, 03909, or 04005.

County Zip Code City State
YORK 03901 BERWICK ME
YORK 03902 CAPE NEDDICK ME
YORK 03902 YORK ME
YORK 03903 ELIOT ME
YORK 03903 ELLIOT ME
YORK 03903 KITTERY ME
YORK 03904 Kittery ME
YORK 03904 KITTERY ME
YORK 03906 NORTH BERWICK ME
YORK 03906 NORTH BERWICK ME
YORK 03908 SOUTH BERWICK ME
YORK 03908 SOUTH BERWICK ME
YORK 03909 York ME
YORK 03909 YORK ME
YORK 04002 ALFRED ME
YORK 04002 LYMAN ME
YORK 04005 BIDDEFORD ME


Thanks for all or your help and time.