Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Data from one field to another

Discussion in 'Information Technology' started by Lesah, Nov 12, 2005.

Tags:
  1. Lesah

    Lesah
    Expand Collapse
    Guest

    I have a very old Access database, that started it's life as an old Q&A
    database. Social Security field is the one in which I do not want
    duplicates. I have a search filter, (will post a filter question on another
    thread), I use to find if someone is already entered. The Q&A format was
    ###-##-####. But after conversion you could only find them using the dashes.
    Then the format from Access stored them in another way, and they cannot be
    found with dashes. There are about 10,000 records with different formats,
    making search more difficult. Can I format a SS field the way I want it, and
    then transfer the data from the old field into that so the formatting will
    all be the same?
    --
    Thanks always to those who share their knowledge with others.

    Lesah
     
  2. Loading...


  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Lesah wrote:
    > I have a very old Access database, that started it's life as an old
    > Q&A database. Social Security field is the one in which I do not want
    > duplicates. I have a search filter, (will post a filter question on
    > another thread), I use to find if someone is already entered. The
    > Q&A format was ###-##-####. But after conversion you could only find
    > them using the dashes. Then the format from Access stored them in
    > another way, and they cannot be found with dashes. There are about
    > 10,000 records with different formats, making search more difficult.
    > Can I format a SS field the way I want it, and then transfer the data
    > from the old field into that so the formatting will all be the same?


    If you run an update query using the Replace() function you should be able to
    replace all instances of "-" with "". That would result in all of the rows
    having no dashes. If you would prefer that they all have the dash then you
    should be able to run another update query using the format property to put them
    back in.

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
  4. Lesah

    Lesah
    Expand Collapse
    Guest

    Thanks Rick. I understand what you are saying and it makes sense. But like
    most replies on this forum, it is a little over my head. I don't know how to
    write modules. Could you possibly post the syntax, (if that is the right
    word), to accomplish this? Unfortunately I have only learned how to use the
    basic tools and formats provided by the program.
    --
    Thanks always to those who share their knowledge with others.

    Lesah


    "Rick Brandt" wrote:

    > Lesah wrote:
    > > I have a very old Access database, that started it's life as an old
    > > Q&A database. Social Security field is the one in which I do not want
    > > duplicates. I have a search filter, (will post a filter question on
    > > another thread), I use to find if someone is already entered. The
    > > Q&A format was ###-##-####. But after conversion you could only find
    > > them using the dashes. Then the format from Access stored them in
    > > another way, and they cannot be found with dashes. There are about
    > > 10,000 records with different formats, making search more difficult.
    > > Can I format a SS field the way I want it, and then transfer the data
    > > from the old field into that so the formatting will all be the same?

    >
    > If you run an update query using the Replace() function you should be able to
    > replace all instances of "-" with "". That would result in all of the rows
    > having no dashes. If you would prefer that they all have the dash then you
    > should be able to run another update query using the format property to put them
    > back in.
    >
    > --
    > I don't check the Email account attached
    > to this message. Send instead to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  5. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Lesah wrote:
    > Thanks Rick. I understand what you are saying and it makes sense.
    > But like most replies on this forum, it is a little over my head. I
    > don't know how to write modules. Could you possibly post the syntax,
    > (if that is the right word), to accomplish this? Unfortunately I
    > have only learned how to use the basic tools and formats provided by
    > the program.


    This isn't code and has nothing to do with modules. What I suggested was
    running an update query against your table. In that update query you can set
    the field equal to the result of passing itself through the Replace function. I
    cannot post what you would see in the query designer in these forums, but the
    SQL of such a query would look something like...

    UPDATE TableName
    SET FieldName = Replace(FieldName, "-", "")

    Essentially this says update FieldName equal to itself with the dashes stripped
    out.

    Test on a copy of your table as update queries done incorrectly could lose all
    your data.

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
  6. Lesah

    Lesah
    Expand Collapse
    Guest

    I tried that and it almost worked, then I got "key violations" error. This
    field is not a primary field, allows duplicates, and there are no
    relationships associated with this table. I have searched the knowledge base
    about key violations and did not find an article directly addressing this.
    This will be my last question I promise.
    --
    Thanks always to those who share their knowledge with others.

    Lesah


    "Rick Brandt" wrote:

    > Lesah wrote:
    > > Thanks Rick. I understand what you are saying and it makes sense.
    > > But like most replies on this forum, it is a little over my head. I
    > > don't know how to write modules. Could you possibly post the syntax,
    > > (if that is the right word), to accomplish this? Unfortunately I
    > > have only learned how to use the basic tools and formats provided by
    > > the program.

    >
    > This isn't code and has nothing to do with modules. What I suggested was
    > running an update query against your table. In that update query you can set
    > the field equal to the result of passing itself through the Replace function. I
    > cannot post what you would see in the query designer in these forums, but the
    > SQL of such a query would look something like...
    >
    > UPDATE TableName
    > SET FieldName = Replace(FieldName, "-", "")
    >
    > Essentially this says update FieldName equal to itself with the dashes stripped
    > out.
    >
    > Test on a copy of your table as update queries done incorrectly could lose all
    > your data.
    >
    > --
    > I don't check the Email account attached
    > to this message. Send instead to...
    > RBrandt at Hunter dot com
    >
    >
    >
     
  7. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Lesah wrote:
    > I tried that and it almost worked, then I got "key violations" error.
    > This field is not a primary field, allows duplicates, and there are no
    > relationships associated with this table. I have searched the
    > knowledge base about key violations and did not find an article
    > directly addressing this. This will be my last question I promise.


    Hmmm. Perhaps try making the query a MakeTable query so that the results end up
    in a totally new table (which should have no rules to break). Then you can
    replace your table with the new one.

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     

Share This Page