Find instances where entries have the same address but different zip codes

[sephiroth2906]

As the title states, I have a database where several entries may have the same address, but someone has been inputting some of the zip codes incorrectly. I want to come up with a way to get a query or the like to spit out a report with just the entries that have identical addresses and conflicting zip codes, and have hit a brick wall.

Can anyone help? I appreciate you taking the time to read this. Thanks!

[June7]

Sounds like structure is not normalized. Why is same address repeatedly typed in entirety? Is this for same customer?

Fixing this probably won't be easy. Are the address parts in separate fields? Show example of raw data.

[sephiroth2906]

The data actually just ports over from Access from an outside program, so, no, it is not normalized. These are parcels, some of which have the same physical address that really serves a much larger area. Here is a small example of what I am dealing with. For my purposes, I am only concerned with Address2 and Zipcode.

Sample.zip

Thanks for taking the time to look at it.

[ssanfu]

I would start by creating a select query with fields: Address1, Address2, City, State, Zip
This is to check the zip code has been entered wrong for an address.

Enter the address, City & state as criteria. If any zip codes are wrong, edit the zip code. If a lot of zip codes are wrong, change the query to (or create) an update query, enter the correct zip code for that address, city, state and execute.

[June7]

Do a count of how many zips for each Address1, Address2, City, State combination.

SELECT Address1, Address2, City, State, Count(Zip) As CountZip FROM tablename GROUP BY Address1, Address2, City, State;

Look at the records where the count is greater than 1. Now you know which Address1, Address2, City, State combinations need to be fixed. Go to the source table and edit records to the correct zip.

Of course, this assumes no errors in the other address parts.

[orange]

Have you considered putting some validation at the source? Who makes/provides the "list" to you? Seems to me that cleaning up that process would make things more efficient.