Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move Data To New Columns (Access 2000 SR1)

    I am combining data from numerous smaller databases into a single database. There are two address fields called Address1 and Address2. There is no consistency as to how data has been entered into the fields. Some have only one address; some have a PO Box and a street address.

    I want to put any PO Box addresses into a new field called Mailing Address and then put any street addresses in a Street Address field for future mail merges.

    Unfortunately, I am drawing a blank about how to accomplish this. Hopefully someone can help me get the lightbulb to go on !?! <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>
    egghead

  2. #2
    Lounger
    Join Date
    Dec 2000
    Location
    Ohio, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    Hello,
    If I understand, when in query set the field to MailingAddress:Address1.
    Note the full colon.
    Hope this refreshed your memory.
    Good Luck

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    First of all I note that you are well versed in the use of outlook. Have you tried importing your data to outlook?

    Secondly, Can you easily identify any of the info? For example, do the PO box numbers start with PO? If you can is it consistent?

    If it is then you could make a query that identifies this and use that to move the info into a new field.

    There is some info on

  4. #4
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    Thanks for the suggestions.

    We have had a data overlay done to the database. This has added all kinds of unique fields of information that would have no match in Outlook.

    The data does have consistent formatting; the PO Boxes all begin "PO Box", etc. So it's pretty easy to filter or query them.

    However, if I set up a new field using MailingAddress: Address1 and put the PO Box as criteria under Address1 as suggested, then how do I get the PO Boxes from Address2 into that same field?
    egghead

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    I
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    It worked! Now to see if I can duplicate what you did! Thank you.
    egghead

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    I took a look at the database you provided containing the demo to try to learn from the example provided.

    Can you explain to me how the following code you have in qryCopy_PO is actually accomplishing what I would think would take two queries to do?

    UPDATE qry_demo1 SET qry_demo1.PO_Box = UpdatePOBox([qry_demo1]![AddrWithPO1],[qry_demo1]![AddrWithPO2],[qry_demo1]![POinPO1],[qry_demo1]![POinPO2]);
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Data To New Columns (Access 2000 SR1)

    You are quite correct it can be done quite elegantly with out any code!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •