Results 1 to 6 of 6
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Update Query? (2003)

    I have a simple task that seems like it could be handled by an update query, but I'm not sure. (Queries are not my strength, and I'm having a hard time understanding update queries from the examples in several books I have.)

    I have a database with about 4,000 records. One of the fields is Address, which usually contains a postal address. About 10% of the records are for persons who are deceased and who have no postal address. For these records, the Address field contains "Obituary nnn", where nnn is a one- to three-digit number referring to an obituary on file for the deceased person.

    My goal is to establish a new field named Obituary, which contains just the obituary number for those who have obituaries. For others, it will be blank. The obituary number should be in text format, because it will be processed later by some text functions.

    It seems I should be able to construct a query that looks at the Address field, and where it finds the word "Obituary" at the start of the field, it should put MID(Address, 8) into the new Obituary field. It should ignore other entries in the Address field.

    Seems simple, but I'm in the dark about it. Any suggestions?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query? (2003)

    First, open the table in design view.
    Add a new field Obituary.
    Leave its Data Type as Text, and set the Size property to the maximum size you need.
    Close and save the table.
    Create a new query in design view.
    Add the table to the query, then close the Show Table dialog.
    Select Query | Update Query to change the query to an update query.
    Add the Address field to the query grid.
    Enter <code>Like "Obituary*"</code> in the Criteria line.
    Add the Obituary field to the query grid.
    Enter <code>Mid([Address], 10)</code> in the Update to line (the 10 is to take the space after 'Obituary' into account, there are 9 characters before it)
    Select Query | Run or click the Run button on the toolbar.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update Query? (2003)

    What you have described seems straight.
    In the address field of the query add a criteria Like "Obituary*"
    Create the obituary before you build the query, then use your mid function to populate this field.

    Before you run the update query save the database first, just in case you need to go back to it prior to running the update query.

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Update Query? (2003)

    It worked! Thanks, guys.

    Like most computer things, it's obvious once you know how to do it. ;-)

    Now, how do I delete all the Addresses that start with "Obituary"?

    I imagine I do another update query with only the Address field in the grid, and "Like Obituary*" in the 'Criteria' box. But what do I put in the 'Update to' box? Will it accept just two double quotes with nothing in between? (I seem to recall some trouble with this.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query? (2003)

    Enter Null in the Update to line.

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Update Query? (2003)

    Worked like magic! Thanks again for the help.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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