Results 1 to 4 of 4
  1. #1
    brobbins is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    3

    Update query with mid, left, and instr functions

    Hello everyone! First post here and hoping to gain some guidance. I'm attempting to insert records based on data from three separate tables. The join conditions are ... odd to say the least. Here's the gist of what I'm trying to do ( I know I cannot use an insert statement this way, I'll get to how I'm trying to re-work here shortly).

    Code:
    insert into i_m
    (i_id, m_id)
    (
    (select i_id 
    from i, sheet1 
    where i_id not in (select i_id from i_m)
    and mid(i.m_number, 5) = left(sheet1.name, itr(1, sheet1.name, '.')-1)
    )
    (select m_id 
    from m 
    where m_id not in (select m_id from i_m)
    )
    )
    The sheet1 table has filenames that correspond with a value in i.m_number. The file names look like "1111.txt" and the i.m_number field looks like "txt 1111" hence the mid, left, and InStr functions.

    This is about as far as I have gotten with the rest of the query as a select statement in an attempt to just get a look at the dataset. Most of my problem with this query is that it takes a VERY long time to run (10 minutes). I can compact & repair the database, but that isn't a maintainable solution to the issue.

    Code:
    select i_id, m_id from i, m, sheet1 
    where i_id not in (select i_id from i_m)
    and mid(m_number, 5) = left(sheet1.name, itr(1, sheet1.name, '.')-1)
    and m_id not in (select m_id from i_m)
    I've also tried working it with joins, but couldn't seem to logic my way through the rest:



    Code:
    select i_id, m_id from m
    inner join sheet1
    on mid(m_number) = left(sheet1.name, itr(1, sheet1.name, '.')-1)
    inner join i
    on i id not in (select i_id from i_m)
    I have a feeling there is some kind of nested inner join I need to do, but I'm not sure how to make it work. Any help with this would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You want to insert data into table i_m from 3 tables: [i], [m] and [sheet1]?

    Could you provide sample data from the 3 tables?

    What data type are i_id and m_id?

    What is the code for itr()?

    Will the file name always be 4 characters to the left and .txt suffix? Here is SQL to join i and sheet1:
    SELECT i_id, m_number, [name] FROM i INNER JOIN sheet1 ON Right(i.m_number,4) & "." & Left(i.m_number,3) = sheet1.name;

    Here I am confused about what you want to do. You want every possible combination of i_id and m_id saved into table i_m?
    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
    brobbins is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    3
    Quote Originally Posted by June7 View Post
    You want to insert data into table i_m from 3 tables: [i], [m] and [sheet1]?
    I thought I did. I was mistaken after a co-worker showed me how it could be done without [sheet1]

    Could you provide sample data from the 3 tables?
    Code:
    [sheet1]         [i].m_number       [m].m_id
    1234.txt         txt 1234               1
    2345.txt         txt 2345               2
    23462.txt       txt 23462             3
    What data type are i_id and m_id?
    Just integers


    What is the code for itr()?
    Sorry, that should have been InStr()

    Will the file name always be 4 characters to the left and .txt suffix?
    It should have been. But there were a few instances where it was not. 15 records out of 5000. Was an easy fix to space them out after finding them.


    Here is SQL to join i and sheet1:
    SELECT i_id, m_number, [name] FROM i INNER JOIN sheet1 ON Right(i.m_number,4) & "." & Left(i.m_number,3) = sheet1.name;
    Thank you!

  4. #4
    brobbins is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    3
    So here's what wound up happening. My co-worker found a very elegant (and MUCH simpler) solution.

    I left out the part where I inserted records into [m]. I chose to do the initial insert all from sheet1 ... and didn't need to do that at all and made things much more complicated than I needed to.

    There is a column available where I can insert i_id as it pertains to m_number from the [i] table into the [m] table. Here's how it should have gone to begin with:

    Code:
    INSERT INTO m(name, i_id_ref)
    SELECT (LTRIM(Mid([m_number], (InStr(2, [m_number], ' ')))) & ".txt") AS name, i_id AS i_id_ref FROM i WHERE m_number <> 'txt ' AND I_ID NOT IN (SELECT I_ID FROM I_M);
    This gives me all the available info in one place, making the next part VERY easy:

    Code:
    INSERT INTO I_M(I_ID, M_ID)
    SELECT i_id_ref as I_ID, M_ID FROM m WHERE i_id_ref IS NOT NULL;
    This works because the I_id_ref is blank for all other entries in the [m] table. Thanks so much for the response June7!

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

Similar Threads

  1. InStr Update Query
    By MTSPEER in forum Queries
    Replies: 9
    Last Post: 08-09-2017, 11:17 AM
  2. Replies: 7
    Last Post: 03-30-2016, 07:39 PM
  3. Left instr help
    By tmcrouse in forum Forms
    Replies: 2
    Last Post: 05-12-2015, 08:47 AM
  4. Replies: 4
    Last Post: 04-22-2015, 05:46 PM
  5. Filtering using IsNumeric and Left functions
    By Kevin Johnston in forum Queries
    Replies: 4
    Last Post: 04-16-2010, 11:44 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