Results 1 to 10 of 10
  1. #1
    Ivan_CRO is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    10

    Query that look for LIKE in another table


    Hi, I have problem with one query...


    I have 2 tables. One table nemed "Customers" witch contain field named "Serial" with 8 characters. In other table named "Counters" I have field named "SerialNumber" wich is 11 characters long.


    What I want to do is connect this two tables with criteria where field "Serial" from table "Customers" look for LIKE in field "SerialNumber" from table "Counters".


    Example... if field "Serial" in table "Customers" have record 12345678, must be matched with record in table "Counters", field "SerialNumber" wich have value 95812345678.


    How to do that?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SQL using LIKE in JOIN clause will have to be constructed in SQL View, this JOIN cannot be built or displayed in Design View.

    SELECT Customers.*, Counters.* FROM Customers INNER JOIN Counters
    ON Counters.SerialNumber LIKE "*" & Customers.Serial;

    Following can be built in Design View:

    SELECT Customers.*, Counters.* FROM Customers,Counters
    WHERE Counters.SerialNumber LIKE "*" & Customers.Serial;
    Last edited by June7; 12-28-2021 at 12:26 AM.
    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
    Ivan_CRO is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    10
    Quote Originally Posted by June7 View Post
    SQL using LIKE in JOIN clause will have to be constructed in SQL View, this JOIN cannot be built or displayed in Design View.

    SELECT Customers.*, Counters.* FROM Customers INNER JOIN Counters
    ON Counters.SerialNumber LIKE "*" & Customers.Serial;
    It seems to be on the trail of what I want, but it is soooo slow that I didn’t get the query result, but cancel after 20 minutes. In the "Customers" table I have approx. 100,000 rows, and in the table "Counters" ca. 20,000. is it possible that it can last that long?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why not simply build a query based on the Counters table with a calculated field stripping out the 8 characters on the right of SerialNumber field and join that to the Customers table?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    but it is soooo slow that I didn’t get the query result,
    And yes, it can take a long time
    A couple of reasons
    1. perhaps the fields concerned aren't indexed
    2. and even if they are, the use of an initial * will negate the use of an index


    possible solutions
    1. apply indexes if not already done so
    2. why do you want to return so many records? reduce the number of records to be returned by using criteria
    3. create a new, indexed field in the counters table consisting of the values required (last 8?) and then join with = rather than Like

    not sure if it would be quicker but you could try

    SELECT Customers.*, Counters.* FROM Customers,Counters
    WHERE Customers.Serial=right(Counters.SerialNumber,8)

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    See if Vlad's suggestion is fast enough. Again, cannot build or display with JOIN in Design View.

    SELECT Customers.*, Counters.* FROM Customers INNER JOIN Counters
    ON Mid(Counters.SerialNumber,4)=Customers.Serial;
    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.

  7. #7
    Ivan_CRO is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    10
    Quote Originally Posted by Ajax View Post
    not sure if it would be quicker but you could try

    SELECT Customers.*, Counters.* FROM Customers,Counters
    WHERE Customers.Serial=right(Counters.SerialNumber,8)
    Works super fast! That's it...

  8. #8
    Ivan_CRO is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    10
    Quote Originally Posted by June7 View Post
    See if Vlad's suggestion is fast enough. Again, cannot build or display with JOIN in Design View.

    SELECT Customers.*, Counters.* FROM Customers INNER JOIN Counters
    ON Mid(Counters.SerialNumber,4)=Customers.Serial;
    Works also super fast! But in the "Counters" table in ca. 30 rows I don't have exactly 11 digits but less..., so Mid(Counters.SerialNumber, 4) doesn't work well for those 30 values. I therefore prefer the example of Ajax user.

    Thanks everyone.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is one faster than other? JOINs are supposed to be more efficient.

    So if you want the right 8 digits, the version using JOIN should work as well as Ajax's. Change the Mid() expression to the Right().
    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.

  10. #10
    Ivan_CRO is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    10
    Quote Originally Posted by June7 View Post
    Is one faster than other? JOINs are supposed to be more efficient.

    So if you want the right 8 digits, the version using JOIN should work as well as Ajax's. Change the Mid() expression to the Right().

    I apologize for the late reply. Both query's work so fast for my amount of data, that query with JOIN takes 1 second, and query with WHERE take 1.3 seconds 😁. So they are actually the same... (for my amount of data).

    Thanks for the tips. 👍

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

Similar Threads

  1. Replies: 3
    Last Post: 03-31-2021, 10:31 AM
  2. Replies: 4
    Last Post: 04-07-2018, 11:55 PM
  3. Replies: 3
    Last Post: 06-01-2017, 06:57 AM
  4. Replies: 2
    Last Post: 01-20-2014, 12:50 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 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