Query to Remove Similar Addresses (Picture)

[cmf0106]

I have a very large database with over 50,000 records. I am querying property addresses such that if the property address and owner address are a mismatch, the query will return a result. I am using Owner address <> [Property Address] to run the query. It works great except for typos present from the source database file.

For example, using the image below
owner address: 1323 "Cimarron Trl"
the property address: "1323 Cimarron Tr"
The only reason the query returns this is as a mismatch is because one is missing the "l" in "trl".

or another example
Property Address: "4706 NE Loraine St"
Owner Address: "4706 E Loraine St"
Again the only reason for this mismatch is because one contains "NE" and the other is missing the "N" in "NE".



Is there a query I could run that could account for these typos?

[June7]

Essentially, no.

That is the issue with erratic inconsistencies. If it was always a case of the missing 'l' then that could be dealt with. But one record is missing the 'l' and another is missing the 'N' (or the N is wrong in the other) and another transposes digits in the building number and some have P.O.Box opposed to building address (which is obviously not an error). Access can't read minds. Fixing the errors can be a tedious and time-consuming effort, if it is really necessary. Is it? Why?

You could do a Find/Replace for every Tr and replace with Trl (or the reverse if you really want Tr). Do that with every error revealed by reviewing the query records, one at a time. Careful, could end up creating worse errors. Consider this address:

4534 Trojan Tr

could result in

4534 Trlojan Trl

[orange]

Further to June7's comment --be very careful with Find and Replace as she pointed out. Isolate what you're intending to find/replace.
eg " Tr " vs "Tr" Note the spaces.

I'm curious of a "premise" in your database:::: Owner address = property address

Certainly someone could own more than 1 property and it would seem to present an anomaly in your database, since the owner could not reside at all properties.

I have had to correct addresses and it can be tedious to impossible. There are Postal standards for addresses and short forms/abbreviations for street types.
Things like Lane, Gardens, Terrace.... have abbreviations, but these are useless if not applied during the input phase.

You can do some things like check the first 10 characters for equality, or the first 15.. or the last 8. It doesn't solve the issue but can help quantify the amount of work involved.

There is nothing short of a miracle that could resolve
7659 Cypress Ct and 420 Simmons Dr

Good luck.

[ItsMe]

I was thinking something like maybe a Mid function followed up with an Intern(PleaseComeHere) function

[June7]

HaHa! Intern, i.e. slave labor.