Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Comparison (Access 97/2000)

    I need to compare a new mailing list with our in-house suppression list and delete matching entries from the new list. In the CompanyName field however, the text may only be a partial match, ie.g. John Brown Construction Ltd in the new table and J Brown Construction (Holdings) in the suppression table. If they are at the same address, I would need to delete the new list entry. How is it possible to match a part entry in one field/one table with a part entry in same field/another table? At the moment, I do this by way of a query, joining the Address1 field in both tables. I put criteria in the CompanyName field in both tables in the design grid, comparing each to the other using a a series of wildcard variations. I then look through the list visually to pick out any matches. Very time-consuming! If I don't put in the wildcard criteria, the result set can show several completely different companies who all have offices at the same address. If I narrow down the list to show just matching or similar entries, this would be a great help. Is this too complicated to be done (and I also realise that I would need to exclude common words like "Limited", "Association", etc.)?

    Any suggestions gratefully received!!!

    Regards, Sue

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Text Comparison (Access 97/2000)

    Hi Sue,
    Probably the best way to do this is to use DAO to step through a recordset that is a query based on an address join and then do a partial match on the first few characters of the name. I don't know how many records you are trying to work with, but it's very many, it can be a real pain to do anything that involves manual intervention for anything more than a handful of records. If you aren't comfortable with DAO, post back and someone can give you some guidance.
    Wendell

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Comparison (Access 97/2000)

    Hi Wendell

    Thanks for your reply. I've had a go using InStr to start at, say, character 4 but what I really need is to be able to compare whole words, rather than part words. If you can supply DAO guidelines, it would be greatly appreciated.

    Thanks, Sue

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Text Comparison (Access 97/2000)

    Well, for starters DAO is a technology that lets you work with records in VBA. Any good book on Access at the advanced level will give you the details - it's much too lengthy a topic to go into details here, but in essence in VBA you define the database object, a recordset object, and then set the recordset object to the results of a query (or a table), and then step through the records doing whatever you need. In your case, you would probably want to open a recordset on your existing table, and then open a query on the new table, perhaps restricting the data to the street address from the current record in your existing table. You might also include a criteria to see if the first word in the company name existed anywhere in the name of the records with the same street address. To determine a word boundary you could use the Instr() function to look for the first space character, and then extract that portion of the string with the Left() function. Note however that even that can get tricky as people sometimes put spaces where they shouldn't be or don't where they should be. Another problem can be the use of commas or dashes. So you probably want to strip those kind of characters out too, and then concatenate an asterisk on both sides to create the criteria. All this assumes that you are comfortable working in VBA and that you are comfortable creating SQL strings in code. Note that I excluded ADO, an alternate data manipulation technology since you apparently work in both 97 and 2000. Hope this makes some sense and is enough to get you started - the final step might be to display records that appear as possible matches in a form so the user can make the final call as to whether it is a duplicate or a unique new record.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •