Results 1 to 6 of 6
  1. #1
    cobiker is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    3

    Update Query

    We are going to start receiving a file from a vendor for upload into another system. They can't seem to get us the file format we want. After a LOT of back and forth I just want to try and do something on our own. I was like "hey, I can do that in Access easy. I used to do stuff like that all the time!" Seems SUPER EASY until I realized I haven't touched an Access Database in like 8 or 9 years......... and apparently have forgotten about everything I knew.

    The basic idea is, import a CSV file (only 3 fields). Code, Amount, Description

    SAMPLE
    1000000001111000,-194.45,SOME TEXT DESCRIPTION

    All I then need to do is manipulate the code field to this format: 10-0-000-000-1111.000


    So I'm inserting hyphens at positions 3, 5, 9, 13
    And inserting a period at position 18

    Seems like a job for an update query?

    Then I'll export it out into a new CSV/txt file for import into our system. Empty out the table, exit, etc...

    I manually imported some sample data for now (will likely create a macro to automate later) and started messing with an update query but can't seem to get the right results. Should I be using the SET command? Am I on the right track?

    UPDATE table
    SET code = Left(code,2) & "-" & ???????

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you'll need a function that you can call in the update query, but that's how I tend to view things. However, I think you should consider linking OR importing the csv file and also have a new table with an additional column to hold the newly formatted data field beside the original format as well as whichever of the csv fields you need. I would not do this on the source file in case anything in the pattern causes unexpected results. This way, not only would the source file be static, you could easily compare the original data against the formatted data in the same table. Should you decide to overwrite the csv file each period, you will also be able to maintain historical records. Others may have a better solution, so I'll wait and see if you need any help on a function.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Am I on the right track?
    An update query is the right track.

    I always use a SELECT query first. Try out this query:
    Code:
    SELECT MainTable.Code, Left(code,2) & "-" & Mid(code,3,1) & "-" & Mid(code,4,3) & "-" & Mid(code,7,3) & "-" & Mid(code,10,4) & "." & Right(code,3) AS TEST
    FROM MainTable;
    If this looks right, then convert the SELECT query to an UPDATE query.
    Code:
    UPDATE MainTable SET MainTable.Code = Left(code,2) & "-" & Mid(code,3,1) & "-" & Mid(code,4,3) & "-" & Mid(code,7,3) & "-" & Mid(code,10,4) & "." & Right(code,3);
    Change the RED to your table name.

  4. #4
    cobiker is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    3
    Quote Originally Posted by Micron View Post
    I think you'll need a function that you can call in the update query, but that's how I tend to view things. However, I think you should consider linking OR importing the csv file and also have a new table with an additional column to hold the newly formatted data field beside the original format as well as whichever of the csv fields you need. I would not do this on the source file in case anything in the pattern causes unexpected results. This way, not only would the source file be static, you could easily compare the original data against the formatted data in the same table. Should you decide to overwrite the csv file each period, you will also be able to maintain historical records. Others may have a better solution, so I'll wait and see if you need any help on a function.
    Thanks, I also thought about keeping the initial table in tact.... safety first!

  5. #5
    cobiker is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    3
    Quote Originally Posted by ssanfu View Post
    An update query is the right track.

    I always use a SELECT query first. Try out this query:
    Code:
    SELECT MainTable.Code, Left(code,2) & "-" & Mid(code,3,1) & "-" & Mid(code,4,3) & "-" & Mid(code,7,3) & "-" & Mid(code,10,4) & "." & Right(code,3) AS TEST
    FROM MainTable;
    If this looks right, then convert the SELECT query to an UPDATE query.
    Code:
    UPDATE MainTable SET MainTable.Code = Left(code,2) & "-" & Mid(code,3,1) & "-" & Mid(code,4,3) & "-" & Mid(code,7,3) & "-" & Mid(code,10,4) & "." & Right(code,3);
    Change the RED to your table name.

    That worked great! Thanks! I might keep it as a select query as mentioned about, then just kick out my new csv file so I can troubleshoot easier.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could add another field to your import table and use the update query to add the formatted code. That way you would have both the org code and the formatted code.
    If you named the new field "CodeFormatted", the update query would be
    Code:
    UPDATE MainTable SET MainTable.CodeFormatted = Left(code,2) & "-" & Mid(code,3,1) & "-" & Mid(code,4,3) & "-" & Mid(code,7,3) & "-" & Mid(code,10,4) & "." & Right(code,3);


    Good luck with your project........

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

Similar Threads

  1. Query Update/Select Query (Wont Update)
    By NickWren in forum Access
    Replies: 1
    Last Post: 03-30-2016, 10:41 AM
  2. Replies: 14
    Last Post: 08-12-2014, 06:33 AM
  3. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  4. Replies: 2
    Last Post: 08-30-2012, 07:59 AM
  5. Replies: 4
    Last Post: 11-10-2011, 03:41 PM

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