Results 1 to 5 of 5
  1. #1
    markdudley is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    San Jose, CA
    Posts
    3

    Query for similar but not exact data

    Hello:

    I have 2 different tables from 2 different entities and I am trying to combine the data. The common field in both tables is Address.
    The problem is that the addresses come from different places and many of them do not mach exactly. For example one table may be Main St and the other may be Main Street

    If I do a one-one relationship an query where both address field are alike, I get about 20,000 of 50,000


    records returned, which is a good start.

    I have not used access since version 2000 and used to be ok, but I found I have forgotten almost everything. Would Excel be better suited for this?

    Thanks in advance for your help.

    Mark

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You will have to 'scrub' the data to standardize the entries. Will be tedious and labor intensive effort. Do Find/Replace to make St into Street, Ave into Avenue (or vice versa). Do Find Unmatched queries to find values not in both tables. Then manually review and make edits as required.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    markdudley is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    San Jose, CA
    Posts
    3
    Quote Originally Posted by June7 View Post
    You will have to 'scrub' the data to standardize the entries. Will be tedious and labor intensive effort. Do Find/Replace to make St into Street, Ave into Avenue (or vice versa). Do Find Unmatched queries to find values not in both tables. Then manually review and make edits as required.
    Thanks for your reply. To make the scrubbing process easier I wanted find which records are matched. I added a field to both tables called match and made it yes/no. Then I linked the 2 tables by the address field and reated an update query to update the matche field to yes in both tables. It updated 30000 records. Yet for each of the individual tables 21000 record on table 1 were updated and 20000 records on table 2 were updated. Shouldn't there have been 30000 in both tables?

    Mark

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't know. Never tried anything like that and don't know your data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    markdudley is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    San Jose, CA
    Posts
    3
    Thanks very much for your replies. In the process of scrubbing the address fileds now. Created a form with 2 subforms side by side witht he address and enlisted half the office to help with the scrubbing. Thanks for your help on this!
    Mark

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-26-2011, 08:56 PM
  2. Replies: 4
    Last Post: 10-24-2011, 11:14 AM
  3. How Merging 3 lists with similar client data?
    By tdaccess in forum Queries
    Replies: 3
    Last Post: 04-13-2011, 09:57 AM
  4. Replies: 3
    Last Post: 02-08-2011, 10:25 AM
  5. similar data rows trouble
    By andyf80 in forum Database Design
    Replies: 3
    Last Post: 06-11-2010, 10:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums