Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    113

    Unanswered: Help with Access aggregate function

    Hey guys,
    im trying to come up with a query that finds all matching companies for 1 table to another. Thing is, even though the naming convention is different in each table they are the same company. What sql command can i use to to find similar company names?? I ellimanted all exact matches so far using this
    select *
    from table1
    where table1.company_name not in
    (select table2.company_name
    from table 2
    where table2.company_name = table2.company_name);

    this query worked but it still gives me a list that is failry large. How do i come up with a sql query that fkinds all similar named companies?? here are ane xample of 2 companies that are the same but named differently:
    SUNRISE GROWERS INC
    SUNRISE GROWERS

    thanks guys

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Use the LIKE operator within your SQL statement. Search your Access Help file about this operator.

    Here is a little example:

    SELECT * FROM Employees WHERE FirstName LIKE '%la%'

  3. #3
    Join Date
    Sep 2004
    Posts
    113
    this command like%table2.name% will give me exact matches. is there something else i can use ?

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    There is no operator that will help you with this kind of match. 'LIKE' will work but in Access it is '*' for the wildcard. The problem is that 'SUNRISE GROWERS*' would match 'SUNRISE GROWERS INC' but the reverse is not true. 'SUNRISE GROWERS INC*' does not match 'SUNRISE GROWERS'. What you want to do can be very complicated and is accomplished using Soundex or a similar algorithm. I use similar software to find duplicates within a mailing list where Robert Smith and Bob Smith would be considered one person at a given address.

    Do you have any other information to match on? An address? If not you might try gettting both lists in a common format and then try the match. For example drop all the 'INC.' and then match.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I put together a little hack-job to take care of a similar situation in my office. We receive a flat file from an external source that does not have any of the primary key information we have in our database, only text names. Some of the names don't match up, so I do something a little different. I pull two datasets into a couple grids (subforms in your case).

    One grid is based on our data, the other the external data. When the user clicks a name from the external data, the grid with our data gets filtere liked so:

    WHERE LEFT(ExternalDataField, 3) IN InternalDataField

    The basic idea is to look at the first three characters of one dataset, then filter the other dataset by finding all names containing those first three letters anywhere in the name.

    I had to do it this way for total compatability, for instance these two would be returned at the same time regardless of order:

    SUN HOMES, LTD & LTD SUN HOMES

    About 80% of the time there is only one matching name. For the rest of the time I simply pick the correct name out of the filtered dataset. I also included a manual filter so the user can type in whatever case-insensitive string they'd like to search for.

    I did this in delphi using datagrids, you can do it in access using subforms just as easily. Come to think of it, it might be easier to use listboxes.
    Last edited by Teddy; 09-21-04 at 16:52.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2004
    Posts
    113
    hey guys..
    thanks for all the useful info but i don't have a copy of vb. What is the best way to handle this using sql queries. So far, i Managed to get the first word from the external data list using left and instr. However alot of the companies in the master list contain "the" or a period after "Inc". Im trying to think the best way to shorten this list through a sql filter. thanks guys

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Clearly you're not familiar with the vba that drives everything behind an ms access application then? You are going to have one HELL of a time getting this done without getting your feet wet in vba.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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