Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Talking Unanswered: Grouping SIMILAR and RELATED records together in Access/Other

    HI all,

    I have no clue where to go with this problem, so I am going to explain what I need as much as I can, and hopefully someone can help me out.

    I have about 500,000 () records in Access DB. I need to group similar records together. I don't see how this would be 100% possible without human supervision, but it would help a lot.

    For example, here is what it looks like now:

    1. Bob's Auto Store | Chicago | IL
    2. Autorama of Chicago | Chicago | IL
    3. Auto Store by Bob | Chicago | IL


    How do I group the two "Bobs" together. What I mean by "group" is putting them next to one other. This can't be done by sorting because some dealerships have multiple variations of their names.

    The goal is to group all "Bob's auto" in Chicago, IL together.

    Now, my problem is not in searching and/or grouping all the entries that contain the words "bob" and "auto", but to group ALL the different records...

    Is there a way to group all SIMILAR records together by somehow defining the similarities?

    It doesn't have to be in Access, I'm open for suggestions and hopefully someone can point me in the right direction!

    Thank you all very much in advance!

    The dude

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by foreigndude
    The goal is to group all "Bob's auto" in Chicago, IL together.
    why do i get the feeling that if you find a solution for this, you will be back right away with a request for "Fred's Restaurant"

    here's your answer:
    Code:
    select ...
      from ...
     where ...
    order by iif(business like '*bob*' and business like '*auto*',0,1), business
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    LOL..

    No actually, I won't ask about Fred's Restaurant. But, I don't think I explained this well..

    In your example, it will sort all Bob's autos together, but only BOB'S. What I want to do is sort all similar businesses together automatically, and have Tony's Car Shop (and its variations), Joe's autorama (and its variations) all DONE as well, without sorting each one individually.

    So it would go something like:

    select ...
    from ...
    where ...
    order by iif(business like '*ALL LIKE BUSINESSES*' and business like '*THAT CONTAIN SOME SIMILARITIES*',0,1), business

    Make sense? (I know.)

    Thank you by the way!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    listen, you know how computers work, they are completely and totally stupid

    there's no way to write a query to get "all similar businesses" unless you define how to decide whether one business name is similar to another

    so, how do you plan to do this?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    outline of a potential scam:

    for each record in tblYours
    use split()-on-space to get the individual words
    soundex the individual words
    save recID; soundex in tmpTable


    ...and no, it's not going to be fast.


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2006
    Posts
    3
    Yup, I agree with that...

    Izy's thing make sense so far, but before I get back here, I'm going to look deeper into this clusterfunk of a database and try and figure out what to base this decision on.

    thanks guys!

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by foreigndude
    Yup, I agree with that...

    Izy's thing make sense so far, but before I get back here, I'm going to look deeper into this clusterfunk of a database and try and figure out what to base this decision on.

    thanks guys!
    Why don't you simply create a field that will categorize each type of business...i.e. Auto, Food, Parts...whatever. Then group on that category. Makes for real simple soup recipe.

    have a nice one,
    BUD

Posting Permissions

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