search and replace query for a value in every column

Discussion in 'Microsoft Access Getting Started' started by mcnews, Jul 31, 2012.

  1. mcnews

    mcnews Guest

    how can i search and replace a value in every or selected columns?

    tia,
    mcnewsxp
     
    mcnews, Jul 31, 2012
    #1
    1. Advertisements

  2. On Tue, 31 Jul 2012 10:31:40 -0700 (PDT), mcnews
    <> wrote:

    >how can i search and replace a value in every or selected columns?


    update thetable
    set selectedcolumn=newvalue
    where selectedcolumn=searchvalue
    and repeat as needed.

    Sincerely,

    Gene Wirchenko
     
    Gene Wirchenko, Jul 31, 2012
    #2
    1. Advertisements

  3. On Tue, 31 Jul 2012 10:31:40 -0700 (PDT), mcnews <>
    wrote:

    >how can i search and replace a value in every or selected columns?
    >
    >tia,
    >mcnewsxp


    How depends on what. In a properly normalized table, replacing a value in
    every column should be a vanishingly rare operation! Each column would be a
    different "Attribute" of the entity represented by the table; it would make no
    sense at all to update (say) the LastName, Birthdate, Height and EyeColor
    fields replacing the same value in all.

    What's the structure of your table, and what change are you proposing?
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Aug 1, 2012
    #3
  4. mcnews

    mcnews Guest

    On Tuesday, July 31, 2012 9:21:34 PM UTC-4, John W. Vinson wrote:
    > On Tue, 31 Jul 2012 10:31:40 -0700 (PDT), mcnews <>
    >
    > wrote:
    >
    >
    >
    > >how can i search and replace a value in every or selected columns?

    >
    > >

    >
    > >tia,

    >
    > >mcnewsxp

    >
    >
    >
    > How depends on what. In a properly normalized table, replacing a value in
    >
    > every column should be a vanishingly rare operation! Each column would bea
    >
    > different "Attribute" of the entity represented by the table; it would make no
    >
    > sense at all to update (say) the LastName, Birthdate, Height and EyeColor
    >
    > fields replacing the same value in all.
    >
    >
    >
    > What's the structure of your table, and what change are you proposing?
    >
    > --
    >
    >
    >
    > John W. Vinson [MVP]
    >
    > Microsoft's replacements for these newsgroups:
    >
    > http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    >
    > http://social.answers.microsoft.com/Forums/en-US/addbuz/
    >
    > and see also http://www.utteraccess.com


    i receive data from different places in different formats that in theory issupposed to be the same data. i have no control how over column names orhow each place stores the same type of info. so much scrubbing is to be done. so after doing the analysis on each data source i can tell what data is junk and needs to vanish.
     
    mcnews, Aug 2, 2012
    #4
  5. On Thu, 2 Aug 2012 09:33:35 -0700 (PDT), mcnews <>
    wrote:

    >i receive data from different places in different formats that in theory is supposed to be the same data.

    i have no control how over column names or how each place stores the same
    type of info. so much scrubbing is to be done. so after doing the analysis
    on each data source i can tell what data is junk and needs to vanish.


    I guess I still don't understand the question.

    It sounds like you have arbitrary tables, with arbitrary and unpredictable
    fieldnames, with arbitrary and unpredictable contents - and you're asking for
    some automated procedure to whip it into shape. I don't think the science of
    artificial intelligence has progressed to that extent!

    Could you please explain what it is you want us volunteers to suggest or
    provide?

    I can imagine a table with fields like Tablename, Columnname, NewColumnName,
    and a form to manage it, but beyond that I'm just not sure what you're asking!
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Aug 2, 2012
    #5
  6. mcnews

    mcnews Guest

    On Thursday, August 2, 2012 1:16:13 PM UTC-4, John W. Vinson wrote:
    > On Thu, 2 Aug 2012 09:33:35 -0700 (PDT), mcnews <>
    >
    > wrote:
    >
    >
    >
    > >i receive data from different places in different formats that in theory is supposed to be the same data.

    >
    > i have no control how over column names or how each place stores the same
    >
    > type of info. so much scrubbing is to be done. so after doing the analysis
    >
    > on each data source i can tell what data is junk and needs to vanish.
    >
    >
    >
    >
    >
    > I guess I still don't understand the question.
    >
    >
    >
    > It sounds like you have arbitrary tables, with arbitrary and unpredictable
    >
    > fieldnames, with arbitrary and unpredictable contents - and you're asking for
    >
    > some automated procedure to whip it into shape. I don't think the science of
    >
    > artificial intelligence has progressed to that extent!
    >
    >
    >
    > Could you please explain what it is you want us volunteers to suggest or
    >
    > provide?
    >
    >
    >
    > I can imagine a table with fields like Tablename, Columnname, NewColumnName,
    >
    > and a form to manage it, but beyond that I'm just not sure what you're asking!
    >
    > --
    >
    >
    >
    > John W. Vinson [MVP]
    >
    > Microsoft's replacements for these newsgroups:
    >
    > http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    >
    > http://social.answers.microsoft.com/Forums/en-US/addbuz/
    >
    > and see also http://www.utteraccess.com


    i worked it out. i just loop through each row of the table and loop through the field names. i then do a string compare with a loaded string array and if i find what is in the array i execute an update statement on the entire column. it works fine.
    thanks.
     
    mcnews, Aug 4, 2012
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lawrence Cosslett

    Search and Replace in Memo Field

    Lawrence Cosslett, Feb 6, 2004, in forum: Microsoft Access Getting Started
    Replies:
    3
    Views:
    1,173
    John Nurick
    Feb 7, 2004
  2. Guest

    How to make a Partial Search Query or partial search function

    Guest, Jun 15, 2004, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    679
    Guest
    Jun 15, 2004
  3. Jeff Boyce

    Re: Replace a number with a letter in Access (Same asEdit/Replace in E

    Jeff Boyce, Dec 11, 2006, in forum: Microsoft Access Getting Started
    Replies:
    0
    Views:
    359
    Jeff Boyce
    Dec 11, 2006
  4. Colin Hayes

    Search and replace empty cells

    Colin Hayes, Apr 8, 2007, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    192
    John W. Vinson
    Apr 8, 2007
  5. Colin Hayes

    open the Find And Replace Dialogue with focus on a specific default column

    Colin Hayes, Mar 18, 2008, in forum: Microsoft Access Getting Started
    Replies:
    3
    Views:
    186
    Larry Daugherty
    Mar 23, 2008
Loading...

Share This Page