Results 1 to 12 of 12
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    252

    Struggling to clean this imported data. Any help is appreciated

    I've got this data that is imported in from excel and I'm struggling to clean it up in the most efficent way. Essentially, the question and answer are both populated in the field, and I'm trying to remove the question and also replace blanks. Here's how the data looks; it's one of these four:


    Was the customer contacted? yes
    Was the customer contacted? no
    Was the customer contacted? n/a
    Was the customer contacted?

    I'm trying to update the above data to:

    yes
    no
    n/a
    unanswered

    I'm really struggling to update the first three in tandem with the last one. Any suggestions? My field name is CustomerContacted.

    I've attached an example DB as well.

    Example22.accdb

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,340
    See query 1 for a starting point
    Attached Files Attached Files
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,126
    Hi

    You could also use an IIf statement

    See Query1
    Attached Files Attached Files

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    252
    So this is basically where I'm stuck. Sorry for not pointing that out. I did the same query as you did but then I can't seem to update the null values to 'unanswered' because they are unupdatable.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,340
    Paste this in

    SELECT tblComplaints.ID,
    tblComplaints.CustomerID,
    tblComplaints.CustomerContacted,
    IIf(Len(Mid([customerContacted],(InStr(1,[customerContacted],"?")+1)))<1,"Unanswered",Mid([customerContacted],(InStr(1,[customerContacted],"?")+2))) AS MyNewField
    FROM tblComplaints;
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,341
    Maybe a bit easier:
    Code:
    UPDATE tblComplaints SET tblComplaints.CustomerContacted = IIf(Right([CustomerContacted],1)="?","Unanswered",Trim(Replace([CustomerContacted],"Was the customer contacted?","")));
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,340
    I was allowing for the question mark to move (assuming there could be a variation in the text, or spelling) hence the Instr(), but that is neater if the text doesn't change.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    252
    I'm going to repost. Your solutions worked in theory but my data is far worse than what I thought. In stems from the excel file; there's like 40 extra spaces at the end of the string.

  9. #9
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,511
    Before bringing your excel data into Access, you may want to bring it into Power Query to do your cleanup and then bring it into Access. Power Query is a powerful tool for doing clean up of Excel files.
    Last edited by alansidman; 05-12-2022 at 12:21 PM.

  10. #10
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,340
    Use Trim() to remove any extra spaces in the results;

    IIf(Len(Mid([customerContacted],(InStr(1,[customerContacted],"?")+1)))<1,"Unanswered",Trim ( Mid([customerContacted],(InStr(1,[customerContacted],"?")+2))) )AS MyNewField

    But agree with Alan - Power query is a good way to normalise data if it isn't normalised.
    Look at the transpose or Unpivot options.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,341
    The one i posted in #6 also uses Trim so it should have removed the extra spaces...

    EDIT: to deal with the extra spaces it needs an extra Trim:
    IIf(Right(Trim([CustomerContacted]),1)="?","Unanswered",Trim(Replace([CustomerContacted],"Was the customer contacted?","")))
    Cheers,
    Last edited by Gicu; 05-12-2022 at 03:16 PM.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,325
    You could use spilt and trim

    Data=trim(split(mystring,?)(1))

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

Similar Threads

  1. Data Clean Up.
    By leungyen in forum Queries
    Replies: 14
    Last Post: 01-12-2015, 01:49 PM
  2. Clean Phone Number data
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 03-11-2013, 10:24 AM
  3. Imported data has different ID
    By cheyanne in forum Forms
    Replies: 48
    Last Post: 05-28-2012, 09:47 AM
  4. Replies: 3
    Last Post: 04-09-2012, 06:05 PM
  5. Data clean
    By derf in forum Programming
    Replies: 0
    Last Post: 09-20-2008, 09:37 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