I am trying to use Access to go through a large database of mailing addresses, and I'm looking to generate a report that looks at all of the mailing addresses that are identical, and then looks for zip codes that do not match. Ideally, I would like to use this same method to check Names vs Mailing Addresses, Mailing Addresses vs Names, etc.
The goal is to clean up the database and drastically reduce postage.
I had thought this was going to be fairly simple, but I haven't come up with a way to do it yet, and I haven't found anything online that tells me anyone else has done this.
My current attempt looks like this: but it is returning zero results.
SELECT [Permissive Export_OLD 1].ACCOUNTNO, [Permissive Export_OLD 1].KEYNO, [Permissive Export_OLD 1].NAME1, [Permissive Export_OLD 1].NAME2, [Permissive Export_OLD 1].ADDRESS1, [Permissive Export_OLD 1].ADDRESS2, [Permissive Export_OLD 1].CITY, [Permissive Export_OLD 1].STATE, [Permissive Export_OLD 1].ZIPCODE
FROM [Permissive Export_OLD 1]
WHERE ((([Permissive Export_OLD 1].ADDRESS2)="EQUAL") AND (Not ([Permissive Export_OLD 1].ZIPCODE)="EQUAL"))
GROUP BY [Permissive Export_OLD 1].ACCOUNTNO, [Permissive Export_OLD 1].KEYNO, [Permissive Export_OLD 1].NAME1, [Permissive Export_OLD 1].NAME2, [Permissive Export_OLD 1].ADDRESS1, [Permissive Export_OLD 1].ADDRESS2, [Permissive Export_OLD 1].CITY, [Permissive Export_OLD 1].STATE, [Permissive Export_OLD 1].ZIPCODE;
I recommend the following query:
SELECT [Permissive Export_OLD 1].ACCOUNTNO, [Permissive Export_OLD 1].KEYNO, [Permissive Export_OLD 1].NAME1, [Permissive Export_OLD 1].NAME2, [Permissive Export_OLD 1].ADDRESS1, [Permissive Export_OLD 1].ADDRESS2, [Permissive Export_OLD 1].CITY, [Permissive Export_OLD 1].STATE, [Permissive Export_OLD 1].ZIPCODE
FROM [Permissive Export_OLD 1], [Permissive Export_OLD 1] AS [Permissive Export_OLD 1_1]
WHERE ((([Permissive Export_OLD 1].ADDRESS2)=[Permissive Export_OLD 1_1].[ADDRESS2]) AND (([Permissive Export_OLD 1].ZIPCODE)<>[Permissive Export_OLD 1_1].[ZIPCODE]))
GROUP BY [Permissive Export_OLD 1].ACCOUNTNO, [Permissive Export_OLD 1].KEYNO, [Permissive Export_OLD 1].NAME1, [Permissive Export_OLD 1].NAME2, [Permissive Export_OLD 1].ADDRESS1, [Permissive Export_OLD 1].ADDRESS2, [Permissive Export_OLD 1].CITY, [Permissive Export_OLD 1].STATE, [Permissive Export_OLD 1].ZIPCODE;
This query compares each row in the table against every other row in the table to find ADDRESS2=ADDRESS2 but ZIPCODE<>ZIPCODE. The query will return all records which fit that criteria as separate rows.