Results 1 to 10 of 10
  1. #1
    muchacho is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    4

    Query Causes Access to Not Respond

    This has to be easier than I am making it. I am trying to run a very simple query that uses a left join on two tables. Table 1 contains serial numbers. Table 2 also contains serial numbers. However, the serial numbers in Table 2 are always longer than those in Table 1. For example:

    Table 1 = 1234567


    Table 2 = WD1234567

    Table one has 5,000 records, and table 2 contains 100,000 records. Below is the SQL Statement for the Query I am using:

    Code:
    SELECT [Table1].[Lot No], [Table1].[Serial No], [Table2].[Serial Number]
    FROM [Table1] LEFT JOIN [Table2] ON [Table2].[Serial Number] LIKE * & [Table1].[Serial No] &*
    WHERE ((([Table1].[Lot No])="ABCDE"));
    .

    If I run the query without the "Like" Operator and wildcard in the statement, it runs fine. But, it does not contain all of the results I need. When I run it with the "Like" and wildcard, Access begins the query, returns results, and then begins to cycle between not responding and and running. I have let it run for a considerable amount of time (1 hr) assuming that it was taking a long time to go through all of the records. However, the cycle never stopped.

    Any help would be great.

  2. #2
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    I can't tell you what might have to be done with the SQL as I always

    work from query design.
    I would solve the problem by creating a calculated field by trimming the leading characters from the serial number and doing the join with = instead of 'Like'

  3. #3
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Not sure you can use the Like condition in the from clause Try this

    SELECT[Table1].[Lot No], [Table1].[Serial No], [Table2].[Serial Number]
    FROM[Table1], [Table2]
    WHERE((([Table1].[Lot No])="ABCDE") AND [table2].[Serial Number] LIKE* & [Table1].[Serial No] &* );

  4. #4
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Also you may want to research on the quotes you may have to use something like this

    Like """*" & [table1].[Serial No] & "*"""

    Not 100% sure of that though

  5. #5
    muchacho is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    4
    Hertfordkc - I originally tried your suggestion. It works. However, the products do not all have the same amount of additional characters. One manufacturer has 3, another 4, and another 6.

    RayMilhon - I assumed that the "like" was the culprit. I originally inserted it from a suggestion on another forum. As soon as it is inserted, Access begins not to respond. When I try the statement the way you suggest I get no results at all. In addition, what I am looking for a examples of when table1 has entries that are not accounted for in table2. Hence the left join.

    I'll keep trying.

  6. #6
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Didn't pay enough attention to the reason for the left join. My bad. change the query to this

    SELECT[Table1].[Lot No], [Table1].[Serial No], [Table2].[Serial Number] FROM[Table1], [Table2]
    WHERE((([Table1].[Lot No])="ABCDE") AND (([table2].[Serial Number] LIKE* & [Table1].[Serial No] &* ) Or ([table2].[Serial Number] Is Null)));

  7. #7
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Is the numeric part always the same number of digits?

    Quote Originally Posted by muchacho View Post
    Hertfordkc - I originally tried your suggestion. It works. However, the products do not all have the same amount of additional characters. One manufacturer has 3, another 4, and another 6.

    RayMilhon - I assumed that the "like" was the culprit. I originally inserted it from a suggestion on another forum. As soon as it is inserted, Access begins not to respond. When I try the statement the way you suggest I get no results at all. In addition, what I am looking for a examples of when table1 has entries that are not accounted for in table2. Hence the left join.

    I'll keep trying.
    And always rightmost? In other words, if you can look at the ID and always extract the numeric part, there should be a function to handle it.

  8. #8
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    How about

    SID=Right(ID,7)
    to get the rightmost 7 digits.

  9. #9
    muchacho is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    4
    Extra characters are always at the left side of the serial number. You are right, there must a function to only like x number of characters counting from right to left.

    When I run the query without the wildcard, I receive 91 results (out of the 5,000) possible. However, as soon as I add "*" &, I get no results.

    I ain't got much hair to begin with. After today, I might not have any. Go Bald Men!!

  10. #10
    muchacho is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    4
    Just found the same function.

    Thank you for helping. Going to run with it. I am sure I will come to another brideg to cross, but this should work for now.

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

Similar Threads

  1. Replies: 12
    Last Post: 05-22-2011, 03:49 PM
  2. Convert Access query to SQL Server query
    By mkallover in forum Queries
    Replies: 2
    Last Post: 05-13-2011, 06:20 AM
  3. Replies: 10
    Last Post: 02-02-2011, 05:48 PM
  4. Replies: 3
    Last Post: 10-04-2010, 01:31 PM
  5. Sql server query to MS access query
    By blazixinfo@yahoo.com in forum Queries
    Replies: 0
    Last Post: 07-07-2009, 08:12 AM

Tags for this Thread

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