Results 1 to 11 of 11
  1. #1
    KelliganP's Avatar
    KelliganP is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    North Carolina, USA
    Posts
    10

    Update Query with Select Function to Complex

    Hi Folks,

    I have a task to update a table based on a variety of conditions. If I was able to put it all in one query the switch function would look like this.

    Code:
    UPDATE tbl_Container SET tbl_Container.Location = Switch(
        [WHSE_ID] Like "*A","SP-01",
        [WHSE_ID] Like "*B","SP-02",
        [WHSE_ID] Like "*C","SP-03",
        [WHSE_ID] Like "*D","SP-04",
        [WHSE_ID] Like "*E","SP-05",
        [WHSE_ID] Like "*F","SP-06",
        [WHSE_ID] Like "*G","SP-07",
        [WHSE_ID] Like "*H","SP-08",
        [WHSE_ID] Like "*I","SP-09",
        [WHSE_ID] Like "*J","SP-10",
        [WHSE_ID] Like "*K","BUILD PAD (SP-11)",
        [WHSE_ID] Like "*L","BUILD PAD (SP-12)",
        [WHSE_ID] Like "*M","SP-13",
        [WHSE_ID] Like "A*","A-Pad",
        [WHSE_ID] Like "B*","B-Pad",
        [WHSE_ID] Like "C*","C-Pad",
        [WHSE_ID] Like "D*","D-Pad",
        [WHSE_ID] Like "E*","E-Pad",
        [WHSE_ID] Like "P*","P-Pad",
        [WHSE_ID] Like "F*","F-Pad",
        [WHSE_ID] Like "Q*","Q-Pad",
        [WHSE_ID] Like "G*","G-Pad",
        [WHSE_ID] Like "H*","H-Pad",
        [WHSE_ID] Like "R*","R-Pad",
        [WHSE_ID] Like "I*","I-Pad",
        [WHSE_ID] Like "J*","J-Pad",
        [WHSE_ID] Like "K*","K-Pad",
        [WHSE_ID] Like "L*","L-Pad",
        [WHSE_ID] Like "M*","M-Pad",
        [WHSE_ID] Like "S*","S-Pad",
        [WHSE_ID] Like "N*","N-Pad",
        [WHSE_ID] Like "X*","X-Pad",
        [WHSE_ID] Like "T*","T-Pad",
        [WHSE_ID] Like "Y*","Y-Pad",
        [WHSE_ID] Like "MR-01","MR-01",
        [WHSE_ID] Like "MR-02","MR-02",
        [WHSE_ID] Like "MR-03","MR-03",
        [WHSE_ID] Like "MR-04","MR-04",
        [WHSE_ID] Like "MR-05","MR-05",
        [WHSE_ID] Like "MR-06","MR-06",
        [WHSE_ID] Like "MR-07","MR-07",
        [WHSE_ID] Like "MR-08","MR-08",
        [WHSE_ID] Like "MR-09","MR-09",
        [WHSE_ID] Like "MR-10","MR-10",
        [WHSE_ID] Like "MR-11","MR-11",
        [WHSE_ID] Like "MR-12","MR-12",
        [WHSE_ID] Like "MR-13","MR-13",
        [WHSE_ID] Like "MR-14","MR-14",
        [WHSE_ID] Like "MR-15","MR-15",
        [WHSE_ID] Like "MR-16","MR-16",
        [WHSE_ID] Like "MR-17","MR-17",
        [WHSE_ID] Like "MR-18","MR-18",
        [WHSE_ID] Like "MR-19","MR-19",
        [WHSE_ID] Like "MR-20","MR-20",
        [WHSE_ID] Like "MR-21","MR-21",
        [WHSE_ID] Like "SURV*","SURV",
        [WHSE_ID] Like "TARF*","TARF",
        [WHSE_ID] Like "TARF2","TARF2",
        [WHSE_ID] Like "ASP","ASP",
        [WHSE_ID] Like "TSA","TSA")

    The issue, of course is that there are more than 29 arguments so I cannot run it.

    I tried to build it out into 5 different update queries but each query when run seems to interfere with the previous or subsequent queries.

    Code:
    UPDATE tbl_Container SET tbl_Container.Location = Switch(
        [WHSE_ID] Like "*A","SP-01",
        [WHSE_ID] Like "*B","SP-02",
        [WHSE_ID] Like "*C","SP-03",
        [WHSE_ID] Like "*D","SP-04",
        [WHSE_ID] Like "*E","SP-05",
        [WHSE_ID] Like "*F","SP-06",
        [WHSE_ID] Like "*G","SP-07",
        [WHSE_ID] Like "*H","SP-08",
        [WHSE_ID] Like "*I","SP-09",
        [WHSE_ID] Like "*J","SP-10",
        [WHSE_ID] Like "*K","BUILD PAD (SP-11)",
        [WHSE_ID] Like "*L","BUILD PAD (SP-12)",
        [WHSE_ID] Like "*M","SP-13",
        [WHSE_ID] Like "A*","A-Pad")
    
    WHERE (((tbl_Container.Location)=""))
    Is there a workaround to accomplish this?



    Thanks and Regards.
    Patrick

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    747
    Well you have conditions like
    Code:
    Like "*A"
    ...
    Like "A*"
    and

    Code:
    Like "M*"
    ...
    Like "MR-01"
    This all looks ambiguous to me. With just one switch statement it will just stop after the first match, multiple queries means records will get matched multiple times.

    You might need to start with clarifying the logic.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,598
    I would resolve the argument for the update outside the sql with Select Case statements, then just use the result of the select case to pass one string to the update.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,901
    You have issues with your 'calculation' see bottom of this post

    you can be smarter with your grouping - for example


    [WHSE_ID] Like "*A","SP-01",
    [WHSE_ID] Like "*B","SP-02",
    [WHSE_ID] Like "*C","SP-03",
    [WHSE_ID] Like "*D","SP-04",
    [WHSE_ID] Like "*E","SP-05",
    [WHSE_ID] Like "*F","SP-06",
    [WHSE_ID] Like "*G","SP-07",
    [WHSE_ID] Like "*H","SP-08",
    [WHSE_ID] Like "*I","SP-09",
    [WHSE_ID] Like "*J","SP-10",

    could be replaced with a single calculation

    right([WHSE_ID],1) IN ("A","B","C","D",E","F","G","H","I","J"),"SP-" & format(asc(right([WHSE_ID],1))-96,"00"),

    and

    [WHSE_ID] Like "A*","A-Pad",
    [WHSE_ID] Like "B*","B-Pad",
    [WHSE_ID] Like "C*","C-Pad",
    [WHSE_ID] Like "D*","D-Pad",
    [WHSE_ID] Like "E*","E-Pad",
    [WHSE_ID] Like "P*","P-Pad",
    [WHSE_ID] Like "F*","F-Pad",
    [WHSE_ID] Like "Q*","Q-Pad",
    [WHSE_ID] Like "G*","G-Pad",
    [WHSE_ID] Like "H*","H-Pad",
    [WHSE_ID] Like "R*","R-Pad",
    [WHSE_ID] Like "I*","I-Pad",
    [WHSE_ID] Like "J*","J-Pad",
    [WHSE_ID] Like "K*","K-Pad",
    [WHSE_ID] Like "L*","L-Pad",
    [WHSE_ID] Like "M*","M-Pad",
    [WHSE_ID] Like "S*","S-Pad",
    [WHSE_ID] Like "N*","N-Pad",
    [WHSE_ID] Like "X*","X-Pad",
    [WHSE_ID] Like "T*","T-Pad",
    [WHSE_ID] Like "Y*","Y-Pad",

    could be replaced with

    left([WHSE_ID],1) IN ("A-N","P-T","X","Y"), left([WHSE_ID],1) & "-Pad",

    (note, need to split "A-N" etc into separate characters)



    These perhaps could be the final 'else' since they are the same
    [WHSE_ID] Like "MR-01","MR-01",
    [WHSE_ID] Like "MR-02","MR-02",
    [WHSE_ID] Like "MR-03","MR-03",
    [WHSE_ID] Like "MR-04","MR-04",
    [WHSE_ID] Like "MR-05","MR-05",
    [WHSE_ID] Like "MR-06","MR-06",
    [WHSE_ID] Like "MR-07","MR-07",
    [WHSE_ID] Like "MR-08","MR-08",
    [WHSE_ID] Like "MR-09","MR-09",
    [WHSE_ID] Like "MR-10","MR-10",
    [WHSE_ID] Like "MR-11","MR-11",
    [WHSE_ID] Like "MR-12","MR-12",
    [WHSE_ID] Like "MR-13","MR-13",
    [WHSE_ID] Like "MR-14","MR-14",
    [WHSE_ID] Like "MR-15","MR-15",
    [WHSE_ID] Like "MR-16","MR-16",
    [WHSE_ID] Like "MR-17","MR-17",
    [WHSE_ID] Like "MR-18","MR-18",
    [WHSE_ID] Like "MR-19","MR-19",
    [WHSE_ID] Like "MR-20","MR-20",
    [WHSE_ID] Like "MR-21","MR-21",
    [WHSE_ID] Like "TARF2","TARF2",
    [WHSE_ID] Like "ASP","ASP",
    [WHSE_ID] Like "TSA","TSA"

    however the 'MR id's will never get assigned because previously you have
    WHSE_ID] Like "M*","M-Pad",

    and TARF2 will never get assigned because it is preceded with

    [WHSE_ID] Like "TARF*","TARF",
    and even that won't happen because before that you have
    [WHSE_ID] Like "T*","T-Pad",

    with also precludes TSA

    and ASP will never happen because previously it will be caught with
    [WHSE_ID] Like "A*","A-Pad",

  5. #5
    KelliganP's Avatar
    KelliganP is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    North Carolina, USA
    Posts
    10
    Thanks for the response kd2017! These are great observations. For the issue...
    Code:
    Like "M*"
    ...
    Like "MR-01"
    ...I wanted to get the first part of this accomplished before I added the additional level of complexity. I will actually need to do something like this with some parts of the switch if it is syntactically possible...
    Code:
    UPDATE tbl_Container SET tbl_Container.Location = Switch(
        [WHSE_ID] Like "L*","L-Pad",
        [WHSE_ID] Like "M*" AND Not Like "MR*","M-Pad",
        [WHSE_ID] Like "S*","S-Pad")
    Is this possible? Is there a better way?

    As far as...
    Code:
    Like "*A"
    ...
    Like "A*"
    ...these are legitimate lines. We have over 300 shipping containers spread out over almost 60 locations. I have a daily data import with these shipping container and need to update the records with an accurate location based on one or more characters in the container name. If a container begins with A, then it is located on A-Pad. If it ends with A, then it is located at SP-01. Any container that starts with A will never end with A and vice versa.

    Do you see an elegant way to accomplish what I need?

    Thanks!
    Patrick

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    747
    Review Ajax's reply, he's found a way to cut down on your code. Maybe you can incorporate that.

    in regard to something like
    Code:
    UPDATE tbl_Container SET tbl_Container.Location = Switch(
        [WHSE_ID] Like "L*","L-Pad",
        [WHSE_ID] Like "M*" AND Not Like "MR*","M-Pad",
        [WHSE_ID] Like "S*","S-Pad")
    Just follow the logic or 'order of operations'. If you match "MR*" first before you match "M*" the switch statement will return before it gets to M* and you don't have to worry about it. BUT if you move these two specific conditions (or any of the conditions that could possibly overlap) to separate queries you'll continue to have problems.

    The issue, of course is that there are more than 29 arguments so I cannot run it.
    At the risk of over-complication you could probably get around this with nested switch statements.

    [EDIT]
    Dave's suggestion, which I think is a custom vba function, might be the easiest to debug and maintain in the future.

  7. #7
    KelliganP's Avatar
    KelliganP is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    North Carolina, USA
    Posts
    10
    Thanks kd2017! I am reviewing Ajax's reply but it is a bit outside my current understanding so I am taking my time to get through it. That is why I have not responded yet. I do intend to get there though. I will also try to process your most recent response and get back with the outcome. Thanks again to both you and Ajax for being so generous with your time!

    Kind Regards,
    Patrick

  8. #8
    KelliganP's Avatar
    KelliganP is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    North Carolina, USA
    Posts
    10
    Hi Dave,

    Thanks for your response. Can you point to a post on the site that does something like this? I have been away from Access and VBA for almost a decade and am not sure how to even begin this.

    Best Regards,
    Patrick

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,598
    The below code would go into a VBA code module. The function will return the location string based on the WHSE_ID
    The update query would simply be

    UPDATE tbl_Container SET tbl_Container.Location = sLocation(WHSE_ID)

    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function sLocation(WHSE_ID as String) As String
        On Error GoTo Error_Handler
        Select Case Left(WHSE_ID, 4)    'target is first 4 characters
            Case "SURV"
                sLocation = "SURV"
            Case "TARF"
                sLocation = "TARF"
        End Select
        
        If Len(sLocation) > 0 Then GoTo Error_Handler_Exit
        
        Select Case Left(WHSE_ID, 2)    'target is first 2 characters
            Case "MR"
                sLocation = WHSE_ID
        End Select
        
        If Len(sLocation) > 0 Then GoTo Error_Handler_Exit
        
        Select Case Mid(WHSE_ID, 2, 1)  'target character not first character
            Case "A"
                sLocation = "SP-01"
            Case "B"
                sLocation = "SP-02"
            Case "C"
                sLocation = "SP-03"
            Case "D"
                sLocation = "SP-04"
            Case "E"
                sLocation = "SP-05"
            Case "F"
                sLocation = "SP-06"
            Case "G"
                sLocation = "SP-07"
            Case "H"
                sLocation = "SP-08"
            Case "I"
                sLocation = "SP-09"
            Case "J"
                sLocation = "SP-10"
            Case "K"
                sLocation = "BUILD PAD (SP-11)"
            Case "L"
                sLocation = "BUILD PAD (SP-12)"
            Case "M"
                sLocation = "SP-13"
        End Select
        
        If Len(sLocation) > 0 Then GoTo Error_Handler_Exit
        
        Select Case Left(WHSE_ID, 1)    'target character is first character
            Case "A"
                sLocation = "A-Pad"
            Case "B"
                sLocation = "B-Pad"
            Case "C"
                sLocation = "C-Pad"
            Case "D"
                sLocation = "D-Pad"
            Case "E"
                sLocation = "E-Pad"
            Case "P"
                sLocation = "P-Pad"
            Case "F"
                sLocation = "F-Pad"
            Case "Q"
                sLocation = "Q-Pad"
            Case "G"
                sLocation = "G-Pad"
            Case "H"
                sLocation = "H-Pad"
            Case "R"
                sLocation = "R-Pad"
            Case "I"
                sLocation = "I-Pad"
            Case "J"
                sLocation = "J-Pad"
            Case "K"
                sLocation = "K-Pad"
            Case "L"
                sLocation = "L-Pad"
            Case "M"
                sLocation = "M-Pad"
            Case "S"
                sLocation = "S-Pad"
            Case "N"
                sLocation = "N-Pad"
            Case "X"
                sLocation = "X-Pad"
            Case "T"
                sLocation = "T-Pad"
            Case "Y"
                sLocation = "Y-Pad"
        End Select
    
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Function
    Error_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sLocation" & "."
        End Select
        Resume Error_Handler_Exit
    End Function

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,222
    Couldn't help but wonder if using Ascii would cut out enough lines to make it viable?
    Case "A',"B","C","D","E","F","G","H","I","J" sLocation = "SP-" & Format(Asc(Mid(WHSE_ID, 2, 1)-64),"00")

    I realize this would be case sensitive but I have no idea if the inputs can only be upper case. Anyway, just musing so feel free to ignore.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,598
    There were some bugs in my previous (untested) posted code. I've revised the code and actually put it to use in a test DB.
    Not many examples of the possible WHSE_ID values provided by OP, so I guessed a few in the example form.

    KelliganP-davegri-v01.zip

    Click image for larger version. 

Name:	kelli.png 
Views:	12 
Size:	22.6 KB 
ID:	44033

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

Similar Threads

  1. "Too complex query" for a Simple Select Query
    By dragon232 in forum Queries
    Replies: 3
    Last Post: 11-29-2016, 02:45 PM
  2. Complex Update Query
    By gemadan96 in forum Queries
    Replies: 5
    Last Post: 11-27-2012, 12:59 PM
  3. Replies: 5
    Last Post: 07-29-2011, 11:54 AM
  4. Duplicate query with complex delete function
    By rushforth in forum Queries
    Replies: 9
    Last Post: 08-20-2010, 01:21 AM
  5. Complex Update query
    By niihla10 in forum Queries
    Replies: 1
    Last Post: 08-28-2009, 01:02 PM

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 - Senior Forums