Select partially matching records

I have two tables, with related records in each table.  Each table consists of property addresses and other property details.  The problem is, the address formatting is not consistent between the two tables.  For example,

Table A
Address:  123 Main St
Sale Price: $1,000,000

Table B
Address:  123 Main Street
Sale Price: $1,000,000

If I was to use the Find Duplicates Query Wizard, the two records listed above would not be identified as being duplicates because of the different formatting of "St" and "Street".  What I would like to do is check for matching records by comparing "123 Main" against "123 Main" which would find a match in the example given.  Additionally, is there a way that I can add a second criteria to check whether the sale price matches?  So, I guess what I am looking for is a query that will check for matching records by comparing the Sale Price fields and the Address fields, but only checking the characters up to the second space in the Address fields.

I hope this makes sense.  Thanks.
The_ShecksterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
You have to include the field common to both table to have an accurate result,
the unique recordID then we can make a query to find duplicates/partial duplicate on specific fields

Post the Table name and fields
0
 
Rey Obrero (Capricorn1)Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.