Results 1 to 15 of 15
  1. #1
    Alaska1 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2010
    Posts
    55

    fuzzy match in Access

    I want to create a query in access that will look up company name but will pick up similar names. I have tried using like clause but it did not work.



    Example
    Utah Savings
    Utah Savings Fund

    Temp Smith Fund
    Temp Smith Inc

    Not an exact name match but of similar.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Post what you tried because LIKE should work for any of what you show, and make sure the sample data you're showing represents the real thing. If it's really people last names, then say so because that could be very significant.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you Googled? Fairly common topic. Start with https://monsiterdex.wordpress.com/20...n-subsequence/
    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.

  4. #4
    Alaska1 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2010
    Posts
    55

    like formula

    Quote Originally Posted by Micron View Post
    Post what you tried because LIKE should work for any of what you show, and make sure the sample data you're showing represents the real thing. If it's really people last names, then say so because that could be very significant.

    Please see attached
    Attached Files Attached Files

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can't equal join those 2 tables on fields that don't contain the same data and expect anything to work. Even if you remove the criteria it will never work as left and right are not equal. Read the sql of the query and you'll probably be able to translate it into logical English and see what I mean. You might be able to use outer joins and get all from one table or the other, plus anything that matches (in this case, LIKE) but what I see in the posted db makes no sense. Surely your data doesn't really look like this?

    I think you're getting ahead of yourself; i.e. you haven't grasped the fundamentals of normalization and table design.

  7. #7
    Alaska1 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2010
    Posts
    55
    The attached is not my data. I cannot post it to the site. I copied over test data but some of it did not copy over. Thank you for taking the time to look at it.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could provide some sample data --even if you make it up-- and a short detailed description of exactly what you want to do. Did you look at the link June provided. Don't overlook Google for finding related info and examples.
    Did you look at the link I suggested, especially the Levenshtein function?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    All that stuff looks too complicated for the data I saw. As I pointed out, the biggest problem has to be that there will never be a match on an equal join on "brown" = "browne". Even if you use LIKE, an equal join will always produce nothing, as would any other join as far as I can see with the sample data.

    Possible solution might be to UNION the two fields and use LIKE in another query. So if
    SELECT fieldA FROM table1
    UNION
    SELECT fieldA FROM table2
    you would get
    browne
    brown
    browning
    brow
    etc.

    Then query that as SELECT * FROM qryMyUnion WHERE theField LIKE "brow*" - or similar. Since the provided data is apparently not representative of what is real, then who knows if that's a realistic solution? I mean, is the goal to weed out mis-spelled words or names, or something else?

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @Micron,

    I tend to agree with possible overkill. But I don't think the OP has shown us the real requirement nor any context for the "matching".

  11. #11
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Sorry Alaska1 but you need to gain a better understanding of MS Access before you continue.

    We will try to help but it is difficult to start at the beginning.

    Do a Google or UTube search on Tutorials. You can look at what you need.

  12. #12
    Alaska1 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Oct 2010
    Posts
    55
    Quote Originally Posted by orange View Post
    Perhaps you could provide some sample data --even if you make it up-- and a short detailed description of exactly what you want to do. Did you look at the link June provided. Don't overlook Google for finding related info and examples.
    Did you look at the link I suggested, especially the Levenshtein function?
    Hello,

    Thank you. I did look at the link.

    I have attached some test data.

    I have two tables one with a unique id for the Company Name the other table is just the company Name. The company name in my data is not an exact match.
    Example
    One table has Utah Savings Fund and the other has Utah Savings.

    In my query it is only returning the exact match.
    I was looking for a way to Match the company name even if it is not an exact match but looks at the first few characters.
    Attached Files Attached Files

  13. #13
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    It would help if you named things correctly. e.g. tblInvestor, tblCompanyName.

    You need a Primary Key in all tables.

    You also need a foreign key in order to create a relationship.

    You must create a Relationship between the two tables. It will not work without it.

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Like

    Hi

    Using Rainlover's guidance this is the outcome.

    Database1.zip

  15. #15
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Naming Conventions (2016_02_04 19_25_13 UTC).doc
    Quote Originally Posted by mike60smart View Post
    Hi

    Using Rainlover's guidance this is the outcome.

    Database1.zip
    That is much better.

    Also do this. Just to make life easier if you were to have a large Database with lots of tables. ( I am talking a dozen or more. )

    Change your naming convention of the Primary and Foreign Keys.

    InvestorPK and InvestorFK. You will see the advantage of this as time goes by.

    I wrote this some years ago.

    Naming Conventions (2016_02_04 19_25_13 UTC).doc

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

Similar Threads

  1. Replies: 7
    Last Post: 03-17-2017, 09:13 AM
  2. Fuzzy mapping_Instr Query
    By google in forum Access
    Replies: 7
    Last Post: 06-27-2016, 05:47 AM
  3. report exported as pdf is fuzzy in Publisher
    By louise in forum Import/Export Data
    Replies: 2
    Last Post: 12-05-2015, 09:25 AM
  4. Fuzzy lookup on field?
    By Poida3934 in forum Forms
    Replies: 2
    Last Post: 10-10-2013, 09:10 PM
  5. Replies: 10
    Last Post: 04-18-2012, 10:55 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